Wrong SQL Syntax?

Question

I am building a small Twitter clone for personal use, and I have so trouble with it. Fist, I want to show you my SQL structure of the table "poke_history":
http://puu.sh/3Sci0.png

This is the command I use to insert the values into a table (in PHP):

$insert = "INSERT INTO poke_history (id, from, time, reason) VALUES ('".$to_id."', '".$from_id."', '".$time."', '".$reason."')";
mysql_query($insert) or die(mysql_error());

This is the annoying error that I am getting:

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 'from, time, reason) VALUES ( '1'' at line 3.

Let me clarify some things.
$to_id is a number.
$from_id is a number.
$time is a number (coming from PHP's time()).
$reason is a text string.

I am using MySQL and PHP5.

Try removing the single quotes for the $to_id and $from_id? Worth a shot. But for future try to use PDO, its much safer. — Ishikawa91, Aug 02 '13 at 21:49
Never mind, thank you guys I used back ticks on the ID and FROM, and it worked. Thanks! — , Aug 02 '13 at 21:51
You are concatenating a string into an SQL statement, which is potentially dangerous. A user entering partial SQL syntax / statements into $reason (or the input field that fills it), he might be able to do harm to your database, or even break the statement in the way that it is happening. — UweB, Aug 02 '13 at 21:52

score 2 · Accepted Answer · answered Aug 02 '13 at 21:53

Try to quote your column identifiers like

INSERT INTO poke_history (`id`, `from`, `time`, `reason`) ...

Everything inside `` is considered to be a "identifier" not a language keyword. From the SQL-syntax it should be clear that after INSERT INTO tablename cannot come a FROM, but the MySQL sometimes needs this kind of guidance (and other sql parsers, too).

score 1 · Answer 2 · answered Aug 02 '13 at 21:51

credit to mario as well:

from is a reserved keyword. Use backticks to escape them.

for example `from`

INSERT INTO table (`from`) ....

So your code would like this:

$insert = "INSERT INTO poke_history (`id`, `from`, `time`, `reason`) VALUES ('".$to_id."', '".$from_id."', '".$time."', '".$reason."')";
mysql_query($insert) or die(mysql_error());

Aris · Answer 3 · 2013-08-02T22:05:39.110

1

$insert = "INSERT INTO poke_history (`id`, `from`, `time`, `reason`) VALUES (".$to_id.", ".$from_id.", ".$time.", '".$reason."')";
mysql_query($insert) or die(mysql_error());

Numbers don't need to be quoted. Only strings. Also don't use mysql, it's deprecated. Better use PDO, with prepared statements, to avoid issues like this.

edited Aug 02 '13 at 22:05

answered Aug 02 '13 at 21:52

Aris

4,643
1
41
38

3

I didn't downvote this, but fwiw you must use the correct type of quotes for quoting identifiers. Use back-ticks with MySQL, not straight single-quotes. Single-quotes are for string literals and date literals. – Bill Karwin Aug 02 '13 at 22:00

score 0 · Answer 4 · answered Aug 02 '13 at 22:38

You should try to use prepared statements to prevent SQL injection.

$query = "
    INSERT INTO
        poke_history (`id`, `from`, `time`, `reason`)
    VALUES
        (:id, :from, :time, :reason)";

$db = new PDO("mssql:host=sqlserver;dbname=database", "username", "password");

$statement = $db->prepare($query);

$parameters = array(
    ":id" => $name,
    ":from" => $from,
    ":time" => $time,
    ":reason" => $reason
);

$statement->execute($parameters);

score -1 · Answer 5 · answered Aug 02 '13 at 22:19

I think that you forgot to add * in between INSERT and INTO, here is the fixed script:

$insert = "INSERT * INTO poke_history (id, from, time, reason) VALUES ('".$to_id."', '".$from_id."', '".$time."', '".$reason."')"; mysql_query($insert) or die(mysql_error());

score -1 · Answer 6 · answered Aug 02 '13 at 22:31

The reason why you are getting the error is because you are trying to use a built in function name for one of your columns. Say you have the following CREATE TABLE...

CREATE TABLE customers
(
name varchar(80),
streetAddr varchar(160),
"from" varchar(60),
);

Notice that to create the table I had to put the column from in quotes. Now if you wanted to insert a row into this table, your insert statement should look like the following:

INSERT INTO ShoppingFun.dbo.customers
(
name, 
streetAddr, 
"from"
) 
VALUES 
(
'MRBubbleGum',
'1061 SW BubbleGumVillage St',
'yourmom'
)

Wrong SQL Syntax?

6 Answers6