PHP doesn't like this code...

[jlewis2k1]

I have gone to number of websites and I see the same example and for whatever reason its a bad code...

	mysql_query("INSERT INTO Information_Center (t_id, title, date, body, author, to) VALUES ('300', '$title', '$date', '$body', '$uid', '$to')") or die("There seems to be a problem with sending your message. Here is the error that was received:<br><BR>\n\n ".mysql_error()."");

this is the error:

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) VALUES ('300', 'test', '12/16/08', at line 1

 

[Nugget]

If any of those fields like t_id or uid are integer values then you shouldn't be escaping them with ticks (') in the SQL statement. You'd have to do something like:

INSERT INTO Information_Center (t_id, title) VALUES (300, 'test');

Also, make damn sure that you've cleaned up those variables with some sort of data hygiene function like mysql_real_escape_string to avoid any risk of sql injection attacks. Depending on where you're getting those values for $title, $body and the rest you might have problems. Imagine someone supplying this book title to your web form:

BOOK'; DROP TABLE Information_Center; --

It doesn't look like php's mysql functions support bound queries (I'm a postgresql guy, myself) which is how I'd expect to do this with a good database. Failing that, you want to clean up those variables to properly escape any special characters like ' or ; so that they aren't interpreted as part of the SQL command.

 

[jlewis2k1]

ok so I took out the quotes, and I am still getting an error.

here is the source code of the script...
http://nfm.myadvatge.com/MessageCenter/new.phps

 

[Nugget]

You took out ALL the quotes, or just the ones for any integer or numeric fields?

That URL is bad. "myadvtge.com" doesn't exist.

 

[jlewis2k1]

erm I just updated the url with the correct address...

http://nfm.myadvatge.com/MessageCenter/new.phps

 

[Knox]

'to' is a reserved word and so you can't use it in column names without using backticks:

INSERT INTO Information_Center (`t_id`, `title`, `date`, `body`, `author`, `to`) VALUES (300, '$title', $date, '$body', $uid, '$to')

(Technically you would only need them on the 'to' column but I would do them all for consistency)

 

[angelwatt]

'to' is a reserved word and so you can't use it in column names without using backticks:

Do you happen to know off hand what the 'to' keyword is for? I'm drawing a blank and that's very hard to Google for. I can only think of 'into.' Good catch by the way.

 

[Knox]

Do you happen to know off hand what the 'to' keyword is for? I'm drawing a blank and that's very hard to Google for. I can only think of 'into.' Good catch by the way.

I did wonder that myself - I was just guessing that it would be reserved based on what point it complained about the query

Looking through the manual there are a few ALTER statements that can/do use TO, but not sure if there are any others.

 

[savar]

If any of those fields like t_id or uid are integer values then you shouldn't be escaping them with ticks (') in the SQL statement.

You can put the numbers in quotes. Mysql is smart enough to figure that out.

 

[SrWebDeveloper]

Since contradicting advice was offered in this topic I created a table in my phpMyAdmin. The following is true based on real queries and testing via MySQL 5.1

If you use any reserved keyword as a table name, it must be wrapped in backticks within any query - but creating any table with reserved keywords is not a good practice (hence topics like this, plus general confusion)

The "To" keyword is certainly reserved, as documented here.

You can insert numbers as strings (wrapped in a quote) into any integer field, just as savar noted.

Nugget hit it dead on about SQL injection protection, fantastic advice. Also, it's strongly recommended that register auto globals be disabled in the PHP ini file. As of later versions of php, register globals is turned off by default, a sensible setting for improved security.

-jim

 

[Yvan256]

http://xkcd.com/327/

 

[jlewis2k1]

LOL I liked that cartoon. Thanks for all of your help everyone. I am now moving on to new parts of the site that I am developing. My deadline is Jan 4th but I have like MAYBE 40% of it done.

 

[InfoTech11]

300 needs to be in '' or ""

 

[primes]

'to' is a reserved word and so you can't use it in column names without using backticks:

INSERT INTO Information_Center (`t_id`, `title`, `date`, `body`, `author`, `to`) VALUES (300, '$title', $date, '$body', $uid, '$to')

(Technically you would only need them on the 'to' column but I would do them all for consistency)

Refer to "MySQL In a Nutshell/O'Reilly" under "TO" in index, it is used in the following commands:

ALTER TABLE
BACKUP TABLE
PURGE MASTER LOGS
RENAME TABLE

 

[SrWebDeveloper]

300 needs to be in '' or ""

That is incorrect. If the field is an integer you do not need to use single or double quotes, not even back ticks for the value. You can and should as part of SQL injection best practices, but it is not required. I tested this in MySQL to be sure.

-jim