Search code examples
mysqlliteralssingle-quotes

Quotation marks for non-string literals in MySQL statements

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?

Solution

  • 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();