mysql_query with quotes around variables?

[giloosh]

I run the code below with cli (no web server)
it works fine on my xampp setup but will not work on my php standalone setup on another machine.

$var1 = "-1";
$var2 = 7;
$q="update `table1` set `fld1` = '$var1' where `fld2` = '$var2'";
mysql_query($q);
 

I am not getting any errors (yes error reporting is on), it just wont update the row.
it works if I type the sql straight into mysql query browser.

update `table1` set `fld1` = '-1' where `fld2` = '7';
 

the funny thing is, if I remove the single quotes from the variables it will work.

$q="update `table1` set `fld1` = $var1 where `fld2` = $var2";

fld1 is TINYINT(1) with unsigned unchecked
fld2 is INTEGER()
I thought php can convert strings into the appropriate data type if needed.
any ideas why its working on xampp and not on a php standalone install?
Thanks for any help!

[Dormilich]

@giloosh
it does, but the problem has a different origin (i.e. it is out of the scope of PHP).

any number types shouldn't be quoted in SQL. but you wrote the quotation marks in the string yourself.... if you use any DAL (database abstraction layer) you can use that to handle quoting for you.

regards

[Atli]

Hi.

Are there any differences between the MySQL servers on those two machines, or the PHP versions?

Try catching the error:

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

See if that tells you anything.

You might also want to consider upgrading to the Improved MySQL extension.
The old MySQL extension was written for MySQL 3, and while it does work with the later versions, it's a bit outdated.

[giloosh]

thanks for the replies. All very useful information.
heres the mysql error I would get:
truncated incorrect double value php mysql
the problem had nothing to do with wraping the variabl in quotes. The variableI was using was extracted from a text file and sent in through a socket over the internet, and somewhere along the way they picked up "whitepsaces" of some sort. When I would echo the $var2 it would show up fine, and the white spaces were invisible to me, so I had no idea. after doing trim($var2) it solved the problem. very weird :-/

[Atli]

Ahh ok. You should be very careful when using "foreign" data like that. There is always the chance that the data is corrupt, or even that it has been altered purposefully to damage your web.

Consider, for example, if somebody had managed to send this as the value for $var2:

1' or 1='1

PHP has some very useful functions to validate data like this.
Like the is_numeric function, which would be ideal for your situation.

Also, remember to always pass all data through the mysql_real_escape_string function before putting it into a query.