Quotation marks for non-string literals in MySQL statements

Question

I know that string literals in MySQL statements need to be included in single quotation marks like this:

SELECT * FROM my_table WHERE str_column='test'

I wonder if there is a good reason to also include non-string literals in such quotes, e.g. like this:

SELECT * FROM my_table WHERE int_column='1'

instead of

SELECT * FROM my_table WHERE int_column=1

Does it make any sense to include non-string literals with single quotes? Or is it just a waste of bytes?

Answer 1

You're right, it's not necessary to quote numeric literals. But I see it done frequently.

There's also no downside to quoting numeric literals. MySQL converts them automatically to a numeric value when they are used in a numeric context, for example comparing to an INT column or used in an arithmetic expression.

I believe the most common case when it's useful to quoting numeric literals is when you're interpolating application variables into SQL strings.

For example:

<?php
$num = $_POST['num'];
$sql = "SELECT * FROM my_table WHERE int_column = $num"; // not safe! SQL injection hazard!

Even if the 'num' parameter is expected to be an integer, a malicious user could pass a string parameter that contains extra SQL syntax, and thus exploit your application.

The fix could be to use real_escape_string() but this assumes that the variable is going to be inside a quoted string in SQL. So you need to quote it in your SQL expression, and then interpolate the escaped value:

<?php
$num = $mysqli->real_escape_string($_POST['num']);
$sql = "SELECT * FROM my_table WHERE int_column = '$num'"; // safer

But it would be equally safe to coerce the numeric variable to a plain integer, which will strip off any non-numeric content:

<?php
$num = (int) $_POST['num'];
$sql = "SELECT * FROM my_table WHERE int_column = $num"; // safe too

And finally, it's safest to use query parameters. Then you don't need to quote the variable, nor escape it:

$num = $_POST['num'];
$sql = "SELECT * FROM my_table WHERE int_column = ?"; // totally safe
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("i", $num);
$stmt->execute();

Answer 2

I use single quotes on all values to protect against future changes to columns. For example, our productID used to be INT,12. At one point we added some services that management wanted to have alpha-numeric product IDs. We changed the productID to VarChar,20 and none of our code skipped a beat.