Variables in MySQL queries & quotes on numbers

PHP
1

Hi,

This are probably 2 very simple questions. But i im not %100 sure of the answers.

1. Should we use quotes for Integers in a MySQL query?

Example:
$query = “SELECT * FROM table WHERE id=‘255’”; // –> with quotes or without around id?

I’ve seen a LOT of answers about this question, but i got confused because some people say that we should use quotes for security reasons, and other people say that we shouldnt because it’s not standard SQL or something.

I know that we should check to see if the value is numeric before using it in a query,etc. But what’s the real answer?

2. I usually insert variables in a query in this 2 forms:

$query = “SELECT * FROM table WHERE id=‘$variable’”;
or
$query = “SELECT * FROM table WHERE id='” . $variable . “'”;

I saw that some people use this:

$query = “SELECT * FROM table WHERE id=‘{variable}’”;

Is that better or the same? It’s a matter of choice or it has a reason?

Thanks for the help! And im sorry for the simple questions.

Nicolas

2

As for number one, don’t quote integers.

For number two, what I do pretty much always, is;


$firstname = 'fred';
$sql = 'SELECT lastname FROM people WHERE firstname=' . $fred . ';
$query = mysql_query($sql);

I might be wrong about question 2, though.

3

hi nico,
it is right that use of quotes are unneccessary. so whenever using a variable which is an integer then don`t use quotes. For 2, if id stores integer then only use the following:


id=$variable
4

Thanks guys for your help!!

1. Should we use quotes for Integers in a MySQL query?

I just found this on MySQL manual: http://dev.mysql.com/doc/mysql/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 record in the table. This exposes every record and causes excessive server load. The simplest way to protect from this type of attack is to use apostrophes around the numeric constants: SELECT * FROM table WHERE ID=‘234’. If the user enters extra information, it all becomes part of the string. In numeric context, MySQL automatically converts this string to a number and strips any trailing non-numeric characters from it.”

So, according to that, we should quote integers in order to have a more secure query. I believe, like you said, it’s not necessary…but i guess it’s a good security practice.
So, unless quoting integers bring any kind of problem that i dont know about, we should quote them for security, right? anybody has something else to say?

2.

My question was specific about this type of query (using brackets {} ):

$query = “SELECT * FROM table WHERE name=‘{$variable}’”

It’s that better than the other two methods? or is it just a matter of choice?

Thanks again!!

5

IIRC, quotying ints only works in MySQL.

As long as you [fphp]intval[/fphp] the number, you should be fine.

There was a post about this a couple of months ago-- I can’t remember the conclusion though.

6

Yes, i believe that quoting numbers only work for MySQL. Although it looks like a good security thing. But i agree that the best is to filter the data before you use it in the query.

Thanks Dylan B.

Nico

7