The right syntax for insertion into Mysql using php

Question

I'm trying to insert some values into the database using information posted on a form through php

following is the code that i'm using for insertion

     $query=mysql_query("select * from poll_question where question = '$question'") or die(mysql_error()); 

    $numrows=mysql_num_rows($query);
     if($numrows)
{
    while($row=mysql_fetch_assoc($query))
    {
    $dbid=$row['id'];

    }
}

    $sql1 = "INSERT INTO poll_option(option , poll_id ,click)
       VALUES('$_POST[optionone]',
             '$dbid' , 0)";
    $result1 = mysql_query($sql1);
    echo "1 record added";
    echo mysql_error();

    $sql2 = "INSERT INTO poll_option(option , poll_id , click)
       VALUES('$_POST[optiontwo])',
             '$dbid', 0)";
    $result2 = mysql_query($sql2);
    echo mysql_error();

    $sql3 = "INSERT INTO poll_option(option , poll_id, click)
       VALUES('$_POST[optionthree])',
             '$dbid ', 0)";
    $result3 = mysql_query($sql3);

    echo mysql_error();

now i'm getting the following output

 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 'option , poll_id ,click) VALUES('sj', '24' , 0)' at line 1
 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 'option , poll_id , click) VALUES('dsdg', '24', 0)' at line 1
 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 'option , poll_id, click) VALUES('xzf', '24 ', 0)' at line 1

The part under the "values" syntax is the one that i'm trying to insert. that information is correct.that is VALUES('xzf', '24 ', 0) is Correct and i want to insert this only , but their is some problem with the syntax.Any suggestions?

You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Apr 28 '13 at 20:54

score 0 · Answer 1 · edited May 23 '17 at 11:57

0

What echo_me said.

Additionally, in $sql2 and $sql3 you are closing the VALUES (...) parenthesis too soon:

VALUES('$_POST[optiontwo])',
                         ^ remove this

Your $sql1 is correct.

edited May 23 '17 at 11:57

Community

1
1

answered Apr 28 '13 at 20:55

Cᴏʀʏ

105,112
20
162
194

score 0 · Answer 2 · answered Apr 28 '13 at 20:55

0

OPTION is reserved keyword for mysql

try use backticks around it in all your queries

like that:

   `option`

look reserved keywords here

answered Apr 28 '13 at 20:55

echo_Me

37,078
5
58
78

score 0 · Answer 3 · answered Apr 29 '13 at 00:28

In addition to what echo_me stated in removing the parentheses incorrectly added to $sql2 and $sql3, you really should migrate over to mysqli (since mysql is deprecated) and at least use the real escape string option on your post variable before automatically inserting whatever is posted to the script into your database. A good example for your code is:

$post_option1 = mysql_real_escape_string($_POST['optionone']);
$post_option2 = mysql_real_escape_string($_POST['optiontwo']);

$sql1 = "INSERT INTO poll_option (`option`, `poll_id`, `click`) VALUES('$post_option1', '$dbid', 0)";
$sql2 = "INSERT INTO poll_option (`option`, `poll_id`, `click`) VALUES('$post_option2', '$dbid', 0)";

My opinion is it would make things simpler for you as well. The info on the real escape string can be found here:

http://php.net/manual/en/function.mysql-real-escape-string.php

It's against best practice to insert a POST or GET directly into your database without any form of mitigation against SQL injection.

score 0 · Answer 4 · answered Apr 29 '13 at 03:22

Try to avoid using mysql functions, but rather learn to use PDO functions. They have a number of advantages over mysql functions, although im really sorry, i dont remember them right now, and i dont want to say anything that's not true.

Also, i dont think that the mysql functions can prevent SQL injection, which can let any user alter your Database however they want.

Most importantly though, is that they're deprecated in PHP 5.5

Sorry if i didn't solve your question, just thought to let you know. Good luck, maybe you can get it to work with the new functions.

Update: Sorry, didn't see the comments and posts about switching to mysqli and such.

The right syntax for insertion into Mysql using php

4 Answers4