Psinyee Psinyee - 3 months ago 11
MySQL Question

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

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:

<form action="read_message.php" method="post">
<table class="form_table">
<tr>
<td style="font-weight:bold;">Subject:</td>
<td><input style=" width:300px" name="form_subject"/></td>
<td></td>
</tr>
<tr>
<td style="font-weight:bold;">Message:</td>
<td id="myWordCount">&nbsp;(300 words left)</td>
<td></td>
</tr>
<tr>
<td><input type="hidden" name="sender_id" value="<?php echo $sender_id?>"></td>
<td><textarea cols="50" rows="4" name="form_message"></textarea></td>
<td valign="bottom"><input type="submit" name="submit_message" value="send"></td>
</tr>
</table>
</form>


Code to insert into a mysql table:

<?php
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 <a href="membership.php?id='.$id.'">shop</a>.';
}
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?

Answer

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.

Comments