...

View Full Version : PHP and MySQL



JimmyS
01-03-2007, 09:48 PM
When I use my php script I get this 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 '12'' at line 1

The code:


$titletablesql = "INSERT INTO TitleTable SET title='$title1', bookid='$bookid'";
mysql_query($titletablesql) or exit(mysql_error());


The 12 is the value for $bookid.

Here are the fields:
titleid - mediumint(10)
title - varchar(100)
bookid - mediumint(10)

For the life of me I can't see what I am doing wrong. I also get the same error on similar things. Could the 12 be a string and not numeric or something?

NOTE: When I manual type the exact same select statment in the command line mysql prompt (with actual values instead of variables) it works.

Brandoe85
01-03-2007, 09:50 PM
Hi,

You've confused the update syntax with the insert syntax, check it out here:
http://dev.mysql.com/doc/refman/5.0/en/insert.html

Good luck;

JimmyS
01-03-2007, 09:56 PM
I'm not updating a existing record. bookid is from another table and is used as the foreign key for this new record.

Brandoe85
01-03-2007, 10:02 PM
I never said you were updating, though you've used the update syntax.

Insert:
insert into tablename(columns) values(values)

Update:
update tablename set column = value, etc...



$titletablesql = "INSERT INTO TitleTable(title, bookid) VALUES('$title1', $bookid)";
mysql_query($titletablesql) or exit(mysql_error());

JimmyS
01-03-2007, 10:05 PM
Oh, I get ya, sorry about that.

I used the same syntax for something else and it work, maybe I should clean that up too.

Thanks for the help, it is much appriciated.

Regards,

GJay
01-03-2007, 10:16 PM
I'm fairly sure INSERT INTO ... SET works in mysql. The change you made, and the cause of the reported error, was the 's around $bookid which, as an int and not a string, doesn't need (and can't have) quotes around it.

If $bookid and $title are coming from user input (a form perhaps?) then they should really be passed through mysql_real_escape_string before being put into the database.
http://php.net/mysql-real-escape-string

Brandoe85
01-03-2007, 10:18 PM
aye, never have I seen it that way. Clarification taken. :)

CFMaBiSmAd
01-03-2007, 10:37 PM
The second syntax definition on the page at the link posted for the mysql manual documents the INSERT INTO SET... syntax. I just checked and this is present in the 4.1 version of the manual as well.

koyama
01-04-2007, 01:51 AM
Hi guys...
Hmmm... I never had any problems with quotes around numeric data in MySQL queries. Strings are apparently converted to numbers before inserting. Actually, it seems that it is better to have quotes around numeric data for better protection against injection.

From the MySQL documentation, it says at some point (http://dev.mysql.com/doc/refman/5.0/en/security-guidelines.html):


A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234, the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1. As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotes around the numeric constants: SELECT * FROM table WHERE ID='234'. If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing non-numeric characters from it.

JimmyS: When I get these kind of errors, I usually print the full query for better viewing


echo $titletablesql;

since the MySQL error message always shows only your truncated query.

JimmyS
01-04-2007, 02:45 PM
I figured out the problem, I was simply just missing a comma. I didn't notice it because I was working on it too long and over looking little things like that. Guess you shouldn't ruch things.

Thanks for the help anyways :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum