How to Insert NULL Value from PHP Variable to MySQL, staying away from SQL Injection?

Question

I have a Song Uploading Form, where I will not directly input NULL value in MySQL like: mysql_query("INSERT INTO songs (album_id) VALUES (NULL)". I will insert NULL from PHP Variable to MySQL, and surely being safe from SQL Injection.

My SQL Table is:

CREATE TABLE IF NOT EXISTS `songs` (
  `song_id` int(4) NOT NULL,
  `song_name` varchar(64) NOT NULL,
  `artist_id` int(4) NOT NULL,
  `album_id` int(4) DEFAULT NULL,
  `genre_id` int(4) DEFAULT NULL
  PRIMARY KEY (`song_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

My FORM and FORM HANDLER Code is (PHP, HTML) like below:

<?php

if(isset($_REQUEST['SongForm']))
{
$song_name = trim($_POST['song_name']);
$artist_id = trim($_POST['artist_id']);
$album_id = $_POST['album_id']; if($album_id == 0) { $album_id = 'NULL'; } // I even tried using NULL instead of 'NULL'
$genre_id = $_POST['genre_id']; if($genre_id == 0) { $genre_id = 'NULL'; }

$query = mysql_query("
INSERT INTO `songs` (`song_name`, `artist_id`, `album_id`, `genre_id`) 
VALUES ('".$song_name."', '".$artist_id."', '".$album_id."', '".$genre_id."')
");
}

?>

<form method="post" action="<?php echo $_SERVER['PHP_SELF']?>" name="SongForm" id="SongForm">
<table style="width: 100%">
<tr>
<td><b>Song Name</b></td>
<td><input name="song_name" value="" type="text" required /></td>
</tr>
<tr>
<td><b>Artist Name</b></td>
<td>
<select id="artist_id" name="artist_id">
<option value ="0">No Artist</option>
<option value ="1">Jennifer Lopez</option>
</select>
</td>
</tr>
<tr>
<td><b>Album Name</b></td>
<td>
<select id="album_id" name="album_id">
<option value ="0">No Album</option>
<option value ="1">Rebirth</option>
</select>
</td>
</tr>
<tr>
<td><b>Genre Name</b></td>
<td>
<select id="genre_id" name="genre_id">
<option value ="0">No Genre</option>
<option value ="1">Epic Records</option>
</select>
</td>
</tr>
<tr>
<td><b>&nbsp;</td></b>
<td><input name="SongForm" type="submit" value="Upload Song" /></td>
</tr>
</table>
</form>

But after this I get the Result in MySQL like:

Serial ID: 1 Song Name Name: I, Love Artist ID: 1 Album ID: 1 Genre ID: 1

Now, when I am NOT SELECTING "Album ID" and "Genre ID" for any song, it should Input "NULL" inside MySQL. But it is inputting "0".

Therefore the result is coming like:

Serial ID: 1 Song Name Name: I, Love Artist ID: 1 Album ID: 0 Genre ID: 0

Please give me a solution so that I can input NULL when I choose "No Album" and "No Genre".

Please don't make me confused explaining irrelevant topics.

Thanks to the friends who supported explaining answers, though any of the answers didn't give me proper solution yet.

Solution 1

In your code, check your variable is empty, if they are empty, use NULL instead of the variable. To pass a NULL to MySQL, try

INSERT INTO table (field,field2) VALUES (NULL,3)

Solution 2

I tried the same thing that you did- set the variable to NULL, null, 'NULL', "NULL", even wrote null as a constant in the SQL string- nothing. The field in the database was always set to 0. Then I tried updating the table after inserting the row and that worked.

"UPDATE songs SET album_id = NULL WHERE etc."

The INSERT statement will insert a 0 if the column is specified at all. You could modify the SQL statement to not include the column that you want to be NULL, for example:

$query = mysql_query("
INSERT INTO `songs` (`song_name`,`artist_id`) VALUES ('".$song_name."','".$artist_id."')
");

and that would leave the remaining columns NULL.

http://www.w3schools.com/sql/sql_insert.asp

skip to Insert Data Only in Specified Columns

Solution 3

use if($album_id == 0) { $album_id = NULL; } instead of if($album_id == 0) { $album_id == "NULL"; } .

"NULL" is a string.

Remove the quotes and change ==(Checking equality) to =(Assigning a value to variable) in $album_id == "NULL";

$album_id = trim($_POST['album_id']); if($album_id == 0) { $album_id = NULL; }
$genre_id = trim($_POST['genre_id']); if($genre_id == 0) { $genre_id = NULL; }
$film_id = trim($_POST['film_id']); if($film_id == 0) { $film_id = NULL; }
$category_id = trim($_POST['category_id']); if($category_id == 0) { $category_id = NULL; }

Comments

[Sneha]

Sorry! This is NOT the ANSWER of my QUESTION actually. I want to store NULL in MySQL from PHP Variable (the Variable will be NULL). Therefore NULL will be inserted from PHP Variable to MySQL.

[Linga]

In your code, check your variable is empty, if they are, use NULL instead of the variable

[Sneha]

Actually from Drop down, when User selects Value "0" I check it with IF-ELSE Statement, then when It is "0" I put $variable = NULL. Then I store $variable inside MySQL. And, in different cases, artist/genre/album any one of those may be empty. So I must have to use $phpVariable inside mysql_query(). So, I can't check it outside using any PHP-Case. If it would be an issue of only one Column, then I could do that. Hope you have understood.

[Sneha]

When I am not selecting "album_id" (therefore the song is not of any album) it should store NULL. But "album_id" is being saved as "0" when I am adding a new Song.