CODEWITHSUNDEEP
mysqlsql
David19801
David19801

Reputation: 11448

Single Quotes in MySQL queries

If I have a MySQL query like:

SELECT this FROM that WHERE id='10'

and

SELECT this FROM that WHERE id=10

both seem to work correctly.

What is the use of the single speech marks in MySQL queries? When is it correct to use them?

Upvotes: 0

Views: 337

Answers (4)

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26699

When MySQL performs the query, there is an implicit conversion of the argument. If id is INT, then '10' is cast to an integer. If id is VARCHAR or another text type, 10 is cast to string. In both cases both queries will work (unless you are running in STRICT mode).

From a performance point of view, you have to use the right data type (do not use quotes for integer arguments) - the implicit cast adds overhead and in some cases, it may hurt the performance of index lookups.

From security perspective, it easier to always use quotes and mysql_real_escape_string (in case the argument is not quoted, mysql_real_escape_string won't stop any attack, that do not use quotes, for example 'UNION SELECT password FROM users'. However, better approach is to cast your variable to int, when it's expected to be int, or use prepared statements

Upvotes: 4

Lucas
Lucas

Reputation: 10646

You should always use them. They can help to stop SQL injection attacks because mysql_real_escape_string isn't enough on its own.

That is assuming you are running a query via PHP.

Upvotes: 0

duplabe
duplabe

Reputation: 36

If the value is a string, you have to use ' or ".

If the value is a number, like in your example, you have not to use ', but MySQL handles it if you put it around 's.

Upvotes: 1

Álvaro González
Álvaro González

Reputation: 146350

Assuming that id is a numeric column, what happens is that MySQL casts your parameter to number automatically so data types match before comparing. It works flawlessly unless casting provides unexpected results. E.g., these expressions with match the row with id=10 because all the strings cast to 10:

id='10'
id='   10'
id='00010'
id='10foo'

The following will not match the row because non-parseable strings cast to 0 and 10<>0:

id='foo10'
id='bar'

When to use each? If you want a string, you need to quote it (there's no other way to type a string and get valid SQL). If you want a number, it must be unquoted (otherwise, you'll get a string that happens to contain a number). Of course, you can always provide numbers as strings and let MySQL do the conversion, but it doesn't really add anything to the query apart from one extra step and possibly incorrect results that go unnoticed.

Upvotes: 1

Related Questions