如何将 NULL 值从 PHP 变量插入 MySQL，远离 SQL 注入? [英] How to Insert NULL Value from PHP Variable to MySQL, staying away from SQL Injection?

问题描述

我有一个歌曲上传表单，我不会在 MySQL 中直接输入 NULL 值，例如:mysql_query("INSERT INTO songs (album_id) VALUES (NULL)". 我会将 NULL 从 PHP 变量插入到 MySQL 中，并且肯定不会受到 SQL 注入的影响.

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.

我的 SQL 表是:

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 ;

我的 FORM 和 FORM HANDLER 代码是(PHP、HTML)，如下所示:

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>

但在此之后我在 MySQL 中得到了结果，例如:

But after this I get the Result in MySQL like:

序列号:1歌名:我，爱艺人编号:1专辑编号:1类型 ID:1

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

现在，当我没有为任何歌曲选择专辑 ID"和流派 ID"时，它应该在 MySQL 中输入NULL".但它正在输入0".

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".

因此结果如下:

序列号:1歌名:我，爱艺人编号:1专辑编号:0类型 ID:0

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

请给我一个解决方案，以便我在选择无专辑"和无流派"时输入NULL.

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.

推荐答案

在你的代码中，检查你的变量是否为空，如果它们为空，使用 NULL 代替变量.要将 NULL 传递给 MySQL，请尝试

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)

这篇关于如何将 NULL 值从 PHP 变量插入 MySQL，远离 SQL 注入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！