在写入 MySQL 数据库时如何处理撇号

我得到了这个错误:

您的 SQL 语法有一个错误; 请检查与 MySQL 服务器版本相对应的手册，以便在第3行使用接近“ s”、“门户”、“提供”、“ MSNBC”、“新闻”、“体育”、“ MSN”、“金钱”、“游戏”的正确语法

唯一的问题是，在插入包含撇号的数据时会显示这个错误。我尝试将数据类型从 VARCHAR更改为 TEXT，但结果仍然相同。

我试过输入 addslashes()

我该怎么补救？

$query=" INSERT INTO alltags
(id,tag1,tag2,tag3,tag4,tag5,tag6,tag7,tag8,tag9,tag10,tag11,tag12,tag13,tag14,tag15,tag16,tag17,tag18,tag19,tag20,tag21,tag22,tag23,tag24,tag25,tag26,tag27,tag28,tag29,tag30)
VALUES
('',mysql_real_escape_string($uniqkey[0]),mysql_real_escape_string($uniqkey[1]),mysql_real_escape_string($uniqkey[2]),mysql_real_escape_string($uniqkey[3]),mysql_real_escape_string($uniqkey[4]),mysql_real_escape_string($uniqkey[5]),mysql_real_escape_string($uniqkey[6]),mysql_real_escape_string($uniqkey[7]),mysql_real_escape_string($uniqkey[8]),mysql_real_escape_string($uniqkey[9]),mysql_real_escape_string($uniqkey[10]),mysql_real_escape_string($uniqkey[11]),mysql_real_escape_string($uniqkey[12]),mysql_real_escape_string($uniqkey[13]),mysql_real_escape_string($uniqkey[14]),mysql_real_escape_string($uniqkey[15]),mysql_real_escape_string($uniqkey[16]),mysql_real_escape_string($uniqkey[17]),mysql_real_escape_string($uniqkey[18]),mysql_real_escape_string($uniqkey[19]),mysql_real_escape_string($uniqkey[20]),mysql_real_escape_string($uniqkey[21]),mysql_real_escape_string($uniqkey[22]),mysql_real_escape_string($uniqkey[23]),mysql_real_escape_string($uniqkey[24]),mysql_real_escape_string($uniqkey[25]),mysql_real_escape_string($uniqkey[26]),mysql_real_escape_string($uniqkey[27]),mysql_real_escape_string($uniqkey[28]),mysql_real_escape_string($uniqkey[29])) ";
mysql_query($query) or die(mysql_error());

我把它改成了 mysql_real_escape_string。这个语法正确吗? 我出错了。

16632 次浏览
小开

Using mysql_real_escape_string is a safer approach to handling characters for SQL insertion/updating:

INSERT INTO YOUR_TABLE
VALUES
(mysql_real_escape_string($var1),
mysql_real_escape_string($var2))

Also, I'd change your columns back from TEXT to VARCHAR - searching, besides indexing, works much better.

Update for your update

Being that id is an auto_increment column you can:

  • leave it out of the list of columns, so you don't have to provide a value in the VALUES clause:

    INSERT INTO alltags
(tag1,tag2,tag3,tag4,tag5,tag6,tag7,tag8,tag9,tag10,tag11,tag12,tag13,tag14,tag15,tag16,tag17,tag18,tag19,tag20,tag21,tag22,tag23,tag24,tag25,tag26,tag27,tag28,tag29,tag30)
VALUES
(mysql_real_escape_string($uniqkey[0]),mysql_real_escape_string($uniqkey[1]),mysql_real_escape_string($uniqkey[2]),mysql_real_escape_string($uniqkey[3]),mysql_real_escape_string($uniqkey[4]),mysql_real_escape_string($uniqkey[5]),mysql_real_escape_string($uniqkey[6]),mysql_real_escape_string($uniqkey[7]),mysql_real_escape_string($uniqkey[8]),mysql_real_escape_string($uniqkey[9]),mysql_real_escape_string($uniqkey[10]),mysql_real_escape_string($uniqkey[11]),mysql_real_escape_string($uniqkey[12]),mysql_real_escape_string($uniqkey[13]),mysql_real_escape_string($uniqkey[14]),mysql_real_escape_string($uniqkey[15]),mysql_real_escape_string($uniqkey[16]),mysql_real_escape_string($uniqkey[17]),mysql_real_escape_string($uniqkey[18]),mysql_real_escape_string($uniqkey[19]),mysql_real_escape_string($uniqkey[20]),mysql_real_escape_string($uniqkey[21]),mysql_real_escape_string($uniqkey[22]),mysql_real_escape_string($uniqkey[23]),mysql_real_escape_string($uniqkey[24]),mysql_real_escape_string($uniqkey[25]),mysql_real_escape_string($uniqkey[26]),mysql_real_escape_string($uniqkey[27]),mysql_real_escape_string($uniqkey[28]),mysql_real_escape_string($uniqkey[29])) ";

  • include id in the list of columns, which requires you use either value in its place in the VALUES clause:

    • NULL
    • DEFAULT

Here's an example using NULL as the id placeholder:

INSERT INTO alltags
(id,tag1,tag2,tag3,tag4,tag5,tag6,tag7,tag8,tag9,tag10,tag11,tag12,tag13,tag14,tag15,tag16,tag17,tag18,tag19,tag20,tag21,tag22,tag23,tag24,tag25,tag26,tag27,tag28,tag29,tag30)
VALUES
(NULL,mysql_real_escape_string($uniqkey[0]),mysql_real_escape_string($uniqkey[1]),mysql_real_escape_string($uniqkey[2]),mysql_real_escape_string($uniqkey[3]),mysql_real_escape_string($uniqkey[4]),mysql_real_escape_string($uniqkey[5]),mysql_real_escape_string($uniqkey[6]),mysql_real_escape_string($uniqkey[7]),mysql_real_escape_string($uniqkey[8]),mysql_real_escape_string($uniqkey[9]),mysql_real_escape_string($uniqkey[10]),mysql_real_escape_string($uniqkey[11]),mysql_real_escape_string($uniqkey[12]),mysql_real_escape_string($uniqkey[13]),mysql_real_escape_string($uniqkey[14]),mysql_real_escape_string($uniqkey[15]),mysql_real_escape_string($uniqkey[16]),mysql_real_escape_string($uniqkey[17]),mysql_real_escape_string($uniqkey[18]),mysql_real_escape_string($uniqkey[19]),mysql_real_escape_string($uniqkey[20]),mysql_real_escape_string($uniqkey[21]),mysql_real_escape_string($uniqkey[22]),mysql_real_escape_string($uniqkey[23]),mysql_real_escape_string($uniqkey[24]),mysql_real_escape_string($uniqkey[25]),mysql_real_escape_string($uniqkey[26]),mysql_real_escape_string($uniqkey[27]),mysql_real_escape_string($uniqkey[28]),mysql_real_escape_string($uniqkey[29])) ";

I want to really stress that you should not setup your columns like that.

小开
最佳答案

The process of encoding data which contains characters MySQL might interpret is called "escaping". You must escape your strings with mysql_real_escape_string, which is a PHP function, not a MySQL function, meaning you have to run it in PHP before you pass your query to the database. You must escape any data that comes into your program from an external source. Any data that isn't escaped is a potential SQL injection.

You have to escape your data before you build your query. Also, you can build your query programmatically using PHP's looping constructs and range:

// Build tag fields
$tags = 'tag' . implode(', tag', range(1,30));


// Escape each value in the uniqkey array
$values = array_map('mysql_real_escape_string', $uniqkey);


// Implode values with quotes and commas
$values = "'" . implode("', '", $values) . "'";


$query = "INSERT INTO alltags (id, $tags) VALUES ('', $values)";


mysql_query($query) or die(mysql_error());
小开

Please look at meagars answer. This is the correct code.

If you want to use the misguided mysql_query() function, then you have to break up the SQL string as follows:

mysql_query(
"INSERT INTO whateever (col1,col2,col3,col4) VALUES ("
. mysql_real_escape_string($col1)
. ","
. mysql_real_escape_string($col2)
. ","
. mysql_real_escape_string($col3)
. ","
. mysql_real_escape_string($col4)
. ")"
);

Or since you have an array, use the clever method call to escape all at once:

$uniqkey = array_map("mysql_real_escape_string", $uniqkey);


mysql_query("USE THE ESCAPED ARRAY THEN DIRECTLY ('$uniqkey[0]', '$uniqkey[1]', '$uniqkey[2]', '$uniqkey[3]', ...");
小开

Slight improvement of meagar's answer:

EDIT: meagar updated his post, so his answer is now better.

$query = 'INSERT INTO alltags (id, ';


// append tag1, tag2, etc.
$query .= 'tag' . implode(', tag', range(1, 30)) . ") VALUES ('', ";


// escape each value in the uniqkey array
$escaped_tags = array_map('mysql_real_escape_string', $uniqkey);


// implode values with quotes and commas, and add closing bracket
$query .= "'" . implode("', '", $escaped_tags) . "')";


// actually query
mysql_query($query) or die(mysql_error());

提交答案