Using PHP variables in SQL

Question

I am trying to use PHP variables in an INSERT SQL statement. Ive seen previous answers to this but can not get mine to work. Here is the code..

mysql_query("INSERT INTO message (message_id, from, content) values ('', " . $uid . ", 'test message content')");

You really don't want to do this. Look at stackoverflow.com/search?q=parameterized+sql instead. — Nate
– Nate, Commented Jul 31, 2012 at 15:55
I'd read up on SQL injection attacks and how to prevent them. This can work - but you need to make sure you are defending against these things as they are very easy to do for an attacker - and what Nate said - parameterized queries — Charleh
– Charleh, Commented Jul 31, 2012 at 15:56

Mark Byers · Accepted Answer · 2012-07-31 15:55:53Z

10

The main problem is that from is a reserved word and should be in backticks.

mysql_query("INSERT INTO message (message_id, `from`, content) VALUES ...");

But I'd also advise you to stop using the deprecated mysql_* functions. I'd recommend that you take a look at PDO and prepared statements with parameters.

answered Jul 31, 2012 at 15:55

Mark Byers

843k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user887958 Over a year ago

Ahhh.. silly me. Cant believe i did not notice the FROM word myself. Thank you!

J A · Accepted Answer · 2012-07-31 15:56:54Z

0

If message_id is primary key, you don't need to include it in the query unless you have a value..

mysql_query("INSERT INTO message (`from`, `content`) values (" . $uid . ", 'test message content')");

answered Jul 31, 2012 at 15:56

J A

1,7661 gold badge12 silver badges13 bronze badges

2 Comments

fvu Over a year ago

message_id, even if it's the PK, isn't necessarily an autoincrement!

Cups Over a year ago

re: auto-increment, with mysql4 an empty string was (incorrectly) permissable. The advent of PHP5 caused this behaviour to stop, so you should either do as J A says or use an integer 0 (unquoted), if it is indeed an auto-increment of course. Caught me out when I upgraded.

Diego · Accepted Answer · 2012-07-31 15:58:23Z

There are at least three issues in your query. Two of them are syntax errors and one is a huge vulnerability.

To make the query work, you should write it as follows:

mysql_query("INSERT INTO message (message_id, `from`, content) values ('', '" . $uid . "', 'test message content')");`

Here's a summary of the errors:
- As another user indicated, "from" is a keyword and you should not use it to name table columns. If you really want to use such name, you must use backticks to indicate it in the query.
- The value of $uid should be enclosed by single quotes.
- The third, and most important error, is that your query is vulnerable to SQL Injection. You should use prepared statements, which would protect you from such attacks.

Collectives™ on Stack Overflow

Using PHP variables in SQL

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related