7

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?

Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
Psinyee
  • 185
  • 1
  • 4
  • 14
  • 3
    You are throwing user input directly into your query. Read up on SQL injection, stop using the deprecated `mysql_` methods, and start using PDO and prepared statements. – Ry- Jun 19 '12 at 02:34
  • 1
    Start by isolating the problem. Which SQL is failing - the UPDATE or the INSERT? Once you figure that out, dump the generated SQL string and take a good look at it. Also, escape your string values with mysql_real_escape_string(). – Seva Alekseyev Jun 19 '12 at 02:34
  • 1
    @minitech not every environment has access to the latest PHP builds. – McGarnagle Jun 19 '12 at 02:35
  • INSERT SQL.How do I apply the mysql_real_escape_string()? – Psinyee Jun 19 '12 at 02:36
  • @dbaseman: The former two parts are requisite, the latter are recommendation. Anyway, PDO has been available since version 5.1, and if you can't get it or enable it, you need to upgrade. – Ry- Jun 19 '12 at 02:37
  • @dbaseman Anyone who's using a PHP install that old is just *asking* for security problems. – Brendan Long Jun 19 '12 at 02:38
  • @minitech agree with you 100% on the SQL injection stuff of course ... I'm just saying, I'm working with a client's Godaddy account, and they're still on PHP 4.0. Sometimes you have to work with what's available. – McGarnagle Jun 19 '12 at 02:38
  • See also ["How does the SQL injection from the “Bobby Tables” XKCD comic work?"](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) – bwDraco Mar 10 '14 at 18:42

5 Answers5

14

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.

Code Magician
  • 23,217
  • 7
  • 60
  • 77
4

That's called SQL INJECTION. The ' tries to open/close a string in your mysql query. You should always escape any string that gets into your queries.

for example,

instead of this:

"VALUES ('$sender_id') "

do this:

"VALUES ('". mysql_real_escape_string($sender_id)  ."') "

(or equivalent, of course)

However, it's better to automate this, using PDO, named parameters, prepared statements or many other ways. Research about this and SQL Injection (here you have some techniques).

Hope it helps. Cheers

Community
  • 1
  • 1
Edgar Villegas Alvarado
  • 18,204
  • 2
  • 42
  • 61
2

I had this problem before, and the reason is very simple: Check your variables, if there were strings, so put it in quotes '$your_string_variable_here' ,, if it were numerical keep it without any quotes. for example, if I had these data: $name ( It will be string ) $phone_number ( It will be numerical ) So, it will be like that:

$query = "INSERT INTO users (name, phone) VALUES ('$name', $phone)"; Just like that and it will be fixed ^_^

Elharony
  • 886
  • 8
  • 18
0

Please make sure you have downloaded the sqldump fully, this problem is very common when we try to import half/incomplete downloaded sqldump. Please check size of your sqldump file.

Vivek Sharma
  • 577
  • 5
  • 9
0

I was getting the same error when I used this code to update the record:

@mysqli_query($dbc,$query or die()))

After removing or die, it started working properly.

Sebastian Lenartowicz
  • 4,695
  • 4
  • 28
  • 39
Shobs
  • 9
  • 1