How to debug an error in SQL syntax?

Question

I'm new to SQL/PHP and I can't get over an error message :

"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 '1',"

I'm trying to debug this error but I look for '1' in my script and I only have this bit :

//enter information into table
$sql = "INSERT INTO $_SESSION[table_name] VALUES 
        ('$_POST[first_name]', '$_POST[last_name]', '$_POST[user_name]', 
           password('$_POST[password]'), 'Administrators', '', '', '0', '$_SESSION[admin_email]',
          '$_POST[redirect_to]', '1', '$date')";

$result = @mysql_query($sql,$connection) or die(mysql_error());

if($result)
{...

I don't find any problem with this.
I don't know how to debug this, how should I proceed to find the error? any clues?

I'm not sure I'm looking to the script in the right place, but this is the only entry where I have '1', that the error message tells me to look at...

The syntax in SQL seems to be correct after checking the manual... I'm using MySQL 5.5.24 in WAMP server.

I'm trying to install "Login-Redirect v1.31" for user authentication.

If anyone can help me I'd really appreciate it!

The first thing you should do is to print out the SQL you're generating - something in there isn't in the format you're expecting. — andrewsi, Aug 27 '12 at 14:57
It may not help answer your question, but you should stop using `mysql_*` functions. They're being deprecated. Instead use [PDO](http://php.net/manual/en/book.pdo.php) (supported as of PHP 5.1) or [mysqli](http://php.net/manual/en/book.mysqli.php) (supported as of PHP 4.1). If you're not sure which one to use, [read this article](http://www.deprecatedphp.com/mysql_/). — Matt, Aug 27 '12 at 15:01
**Your code is vulnerable to SQL injection.** You *really* should be using [prepared statements](http://stackoverflow.com/a/60496/623041), into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of [Bobby Tables](http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain). — eggyal, Aug 27 '12 at 15:08

score 9 · Accepted Answer · answered Aug 27 '12 at 14:56

9

STOP

Before you go any farther with this code, read up about SQL injection attacks, and FIX YOUR CODE

Your syntax error is almost certainly caused by an injection fault, undoubtedly from an extra ' somewhere in the data you're inserting into your query. You are passing in raw user-supplied data into the query, allowing a malicious user to take over your server, destroy your database, kick your dog, etc...

Beyond this, do an echo $sql and paste the results here, we'll be able to show you exactly where the bad ' is.

answered Aug 27 '12 at 14:56

Marc B

356,200
43
426
500

I'll read up about injection attacks. Thanks! The $echo prints : INSERT INTO authorize VALUES ('Francisco', 'Arantes', 'myusername', password('********'), 'Administrators', '', '', '0', 'mymail@mail.com', ' and stops. – Francisco Aug 27 '12 at 15:07
do a 'view source' on the page in your browser - there's no reason for the sql statement to just "stop", unless there's html or other stuff that looks like html tags which your browser is hiding. That'd explain the `>` that shows up in your error message. never EVERy trust what your browser shows - always look at the page source. – Marc B Aug 27 '12 at 15:15
Thanks! I got the answer but can't upload for 8 hours cause I'm new and haven't got enough reputation points. After the printing the echo statement I realized the problem was with the $_POST[redirect_to] that was not well defined. – Francisco Aug 27 '12 at 15:32
While injection attacks is the prime reason for using parameterised queries, how much easier is it to see what's wrong where, shouldn't be discounted. – Tony Hopkinson Aug 27 '12 at 16:17

score 0 · Answer 2 · answered Aug 27 '12 at 14:58

The first step would be to isolate what part of the query is referencing.

Your error is referencing a column, and the insertion of a field.

I see you are passing a '1'. '1' is a string, whereas 1 is an integer.

If you Column Schema says it is a int column type, then removed the single quotes and try again.

score 0 · Answer 3 · answered Aug 27 '12 at 14:59

For starters do this:

Use an echo statement to print $sql to screen (or log it to file).
Copy the statement to your favourite SQL editor and work through it, trying to find the error.

It's hard to debug a dynamic SQL statement without knowing what variables bind to when code is executed.

score 0 · Answer 4 · edited Aug 28 '12 at 19:38

Thank you all so much for helping me with this problem.

Printing an $echo on the variable gave me the answer. the problem is with the $_POST[redirect_to] that is not working because it was not well defined before.

I changed the input variable to an empty string and it worked :

$sql = "INSERT INTO $_SESSION[table_name] VALUES 
        ('$_POST[first_name]', '$_POST[last_name]', '$_POST[user_name]', 
        password('$_POST[password]'), 'Administrators', '', '', '0', 
        '$_SESSION[admin_email]', ' ', '1', '$date')";

$result = @mysql_query($sql,$connection) or die(mysql_error());

if($result){
    // ...
}

I'll study the vulnerability to SQL injections and work it out, I'm very new to this.

Thank you for the correction and tips!

How to debug an error in SQL syntax?

4 Answers4