[RESOLVED] error 1064 and data not entering into db

termy_raf

Hi all

I've just noticed 2 small programs of mine stopped working. They share identical problems.

Both cannot insert data into the database. A new row is created, yet no data is inserted.

Code example is below.


$query = "insert into softserve_user

(name, homepage) values

('$name','$homepage')"

;

Both cannot retrieve data from a populated row in the database

errorno=1064
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 '' at line 1
query=select px_x from softserve_object where number=

Code example is below.

$query = "select px_x from softserve_object where number=$what";

The host is using PHP Version 5.1.6 and MySQL 4.1

Is this due to my host updating either php or mysql? If so - what changes to the code need to be made?

Thanks in advance!

bogu

U should check for $what not to be [man]empty[/man] and check when it become empty ...

termy_raf

Thanks bogu

$what is never empty as it is passed by the browser

e.g.
view_swf.php?who=5&what=290026

...

sneakyimp

You should check the MySQL documentation when you get an error code to see what the error code means:

http://dev.mysql.com/doc/

Apparently the error means that mysql could not parse your query because the syntax of the query isn't proper SQL syntax. this can happen if either $name or $what has a single quote in it...like this:

$query = "insert into softserve_user
(name, homepage) values
('Here's some syntax that is broken','http://foo.bar.com')";

the basic idea is that the apostrophe in "Here's" makes MySQL think that your value for name has ended and then all the text after it doesn't look like proper sql syntax.

You should escape any text fields with mysql_real_escape_string() before sticking them in a query -- ESPECIALLY if that text comes from user input because if you don't, people can easily do SQL INJECTION on your database. google it if you don't know what i'm talking about.

Try echoing the query to see what query is actually trying to run and maybe let us see it? OR try queries like this:

$query = "insert into softserve_user
(name, homepage)
values
('" . mysql_real_escape_string($name) . "', '" . mysql_real_escape_string($homepage) . "')";

the other query had no quotes around $what. that would probably never work if $what was text.

$query = "select px_x from softserve_object where number=$what";

try this instead

$query = "select px_x from softserve_object where number='" . mysql_real_escape_string($what) . "'";

bradgrafelman

termy_raf wrote:
$what is never empty as it is passed by the browser

e.g.
view_swf.php?who=5&what=290026

So? That doesn't mean it's never empty. In fact, if your host is smart, $what wouldn't even be defined. $_GET['what'] on the other hand, would.

sneakyimp wrote:
the other query had no quotes around $what. that would probably never work if $what was text.

Well, since the column name "number" is probably of a numeric type, its values shouldn't have quotes. I agree, though, if someone altered the query string on their own they might try injecting text. I would simply use a function like [man]intval/man to convert $_GET['what'] to an integer and pass that into the query string (without quotes).

As for your INSERT problems, I would definitely follow sneakyimp's advice and sanitize the data (assuming it's provided by the user) with a function such as [man]mysql_real_escape_string/man. Also, I'm betting you might have the same issues as above -- you're relying on register_globals to be on and therefore automatically populate the 'name' and 'homepage' variables. Instead, you should be using the appropriate superglobal array ($GET, $POST, $_COOKIE, etc. etc.). For more info on these arrays, visit this man page: [man]variables.predefined[/man].

For more information about why register_globals is a very very very bad directive to have enabled, use any search engine (or even search this board) for keywords such as "php register globals security", etc.

termy_raf

Thanks everyone for your replies - I definately have enough here to work on
many thanks!

termy_raf

I think my host has recently become smart - changing to $_GET['what'] means I can now retrieve data (this is what was causing the 1064)

Also now testing for an integer - - - and will soon check if it is empty or not.

So that's half my problem solved... ta.