mysql插入失败39,您的SQL语法有误；请检查与您的MySQL服务器版本相对应的手册，以在&＃39;&＃39;&＃39;&＃39;）&＃39;中使用正确的语...

I am getting an Error in MySQL:

You have an error in your SQL syntax; check the manual that corresponds

to your MySQL server version for the right syntax to use near '''')' at line 2'.

HTML Code:

Code to insert into a mysql table:

include_once"connect_to_mysql.php";

//submit new message

if($_POST['submit_message']){

if($_POST['form_subject']==""){

$submit_subject="(no subject)";

}else{

$submit_subject=$_POST['form_subject'];

}

$submit_message=$_POST['form_message'];

$sender_id = $_POST['sender_id'];

if($shortMessagesLeft<1){

$form_error_message='You have left with '.$shortMessagesLeft.' Short Message. Please purchase it from the shop.';

}

else if($submit_message==""){

$form_error_message = 'Please fill in the message before sending.';

}

else{

$message_left = $shortMessagesLeft-1;

$update_short_message = mysql_query("UPDATE message_count SET short_message = '$message_left' WHERE user_id = '$id'");

$sql = mysql_query("INSERT INTO private_messages (to_id, from_id, time_sent, subject, message)

VALUES('$sender_id', '$id', now(),'$submit_subject','$submit_message')") or die (mysql_error());

}

}

?>

What does the error mean and what am I doing wrong?

解决方案

There is a single quote in $submitsubject or $submit_message

Why is this a problem?

The single quote char terminates the string in MySQL and everything past that is treated as a sql command. You REALLY don't want to write your sql like that. At best, your application will break intermittently (as you're observing) and at worst, you have just introduced a huge security vulnerability.

Imagine if someone submitted '); DROP TABLE private_messages; in submit message.

Your SQL Command would be:

INSERT INTO private_messages (to_id, from_id, time_sent, subject, message)

VALUES('sender_id', 'id', now(),'subjet','');

DROP TABLE private_messages;

Instead you need to properly sanitize your values.

AT A MINIMUM you must run each value through mysql_real_escape_string() but you should really be using prepared statements.

If you were using mysql_real_escape_string() your code would look like this:

if($_POST['submit_message']){

if($_POST['form_subject']==""){

$submit_subject="(no subject)";

}else{

$submit_subject=mysql_real_escape_string($_POST['form_subject']);

}

$submit_message=mysql_real_escape_string($_POST['form_message']);

$sender_id = mysql_real_escape_string($_POST['sender_id']);

Here is a great article on prepared statements and PDO.