When to surround SQL fields with apostrophes?

Question

I notice that when I INSERT and SELECT values to and from a database I have to surround the fields with single quotes, like so:

mysql_query("INSERT INTO employees (name, age) VALUES ('$name', '$age')");

However, if I were to update the age, I would not use single quotes:

mysql_query("UPDATE employees SET age = age + 1 WHERE name = '$name'");

Also, it seems when adding the date to a SQL database I do not have to surround it with single quotes either:

mysql_query("INSERT INTO employees (name, date) VALUES ('$name', NOW())");

Also, when using operators like CONCAT it seems not to be necessary either:

mysql_query("UPDATE employees SET name=CONCAT(name,$lastName) WHERE id='$id'");

Perhaps I am just coding poorly but I seem to recall if I did not surround a field with single quotes when inserting and selecting it the operation failed.

Answer 1

You need to surround the values with quotes when field data type is of string eg text, char, varchar, etc or date types such as date, time, datetime.

For numerical types such as int, bigint, decimal, etc or SQL functions such as now(), current_date, you don't need quotes.

Answer 2

"age" exists in the question as both a php variable ($age) and as a MySQL column name. Column names shouldn't be quoted (generally speaking) but the contents of a column, used in a select or insert statement, ought to be quoted.

In particular, if the contents of a php variable haven't been set, the variable itself will vanish and this can break your syntax. Surrounding php variables with single quotes will at least protect the syntax in case the variable vanishes.

SELECT * from something where age = $age;

If for some reason $age wasn't set, such as the user didn't enter it on input, it will simply vanish and this line of code will produce a syntax error at run time because it becomes "where age = ;"

SELECT * from something where age = '$age';

If for some reason $age wasn't set, it will disappear but won't generate an error because it will become "where age = '';" and is still good syntax.

SQL injection is still possible in this instance of course but that's a different question.