1

I'm writing a piece of code that allows users to send messages to each other. Whenever I try to insert the message into the database, I get a syntax error, but I, for the life of me, cannot figure out what my error is. I know that the issue is not within connect.php. Also, I am getting the appropriate values for $from, $to, and $message so that can't be the issue. Here is my code:

session_start();

require_once('../setup/connect.php');

$from = $_SESSION['id'];
$to = $_REQUEST['id'];

$message = trim($_POST['msg_body']);    
$insert = "INSERT INTO messages(to, from, msg) VALUES('$to', '$from', '$message')";

mysql_query($insert) or die(mysql_error());

header("Location: view_profile.php?id=$to");

Here is the report mysql_error() generates:

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 'to, from, msg) VALUES('7', '6', 'Hey how are you?')' at line 1

And here is an image of my database structure:enter image description here

I appreciate any help!

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Matt Altepeter
  • 317
  • 2
  • 5
  • 17
  • I noticed your msg column uses varchar(2000). It might be more efficient to change it to TEXT instead. Just a suggestion! – Shawn Cheever Jan 22 '13 at 15:48
  • This code needs a lesson from [Bobby Tables](http://bobby-tables.com/). – datasage Jan 22 '13 at 15:49
  • You are leaving yourself wide open to SQL injection. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php.html has examples to get you started. – Andy Lester Jan 22 '13 at 15:50
  • @ShawnCheever: I suggest that if there's not a specific, measurable win in changing from VARCHAR to TEXT, the "more efficient" is a premature optimization. – Andy Lester Jan 22 '13 at 15:51

2 Answers2

5

TO and FROM are reserved keywords, it must be escaped with backtick

INSERT INTO messages(`to`, `from`, msg) 
VALUES('$to', '$from', '$message')

If you have time or privilege to alter, don't use such names that are present on the reserved keyword list. It will give you future head aches.

As a sidenote, the query is vulnerable with SQL Injection if the value(s) came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
1

FROM is a reserved word. You shouldn't use it as a column name. If you insist on using it you have to quote it:

INSERT INTO messages("to", "from", msg) 
VALUES('$to', '$from', '$message')

or if you are not running your MySQL installation in ANSI mode you will have to use the dreaded non-standard backticks:

INSERT INTO messages(`to`, `from`, msg) 
VALUES('$to', '$from', '$message')
John Woo
  • 258,903
  • 69
  • 498
  • 492