Quotation marks for non-string literals in MySQL statements -

i know string literals in mysql statements need included in single quotation marks this:

select * my_table str_column='test' 

i wonder if there reason include non-string literals in such quotes, e.g. this:

select * my_table int_column='1' 

instead of

select * my_table int_column=1 

does make sense include non-string literals single quotes? or waste of bytes?

you're right, it's not necessary quote numeric literals. see done frequently.

there's no downside quoting numeric literals. mysql converts them automatically numeric value when used in numeric context, example comparing int column or used in arithmetic expression.

i believe common case when it's useful quoting numeric literals when you're interpolating application variables sql strings.

for example:

<?php $num = $_post['num']; $sql = "select * my_table int_column = $num"; // not safe! sql injection hazard! 

even if 'num' parameter expected integer, malicious user pass string parameter contains sql syntax, , exploit application.

the fix use real_escape_string() assumes variable going inside quoted string in sql. need quote in sql expression, , interpolate escaped value:

<?php $num = $mysqli->real_escape_string($_post['num']); $sql = "select * my_table int_column = '$num'"; // safer 

but equally safe coerce numeric variable plain integer, strip off non-numeric content:

<?php $num = (int) $_post['num']; $sql = "select * my_table int_column = $num"; // safe 

and finally, it's safest use query parameters. don't need quote variable, nor escape it:

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