When to use single quotes in an SQL statement?

Question

I know that I should use it when I deal with data of TEXT type (and I guess the ones that fall back to TEXT), but is it the only case?

Example:

UPDATE names SET name='Mike' WHERE id=3

I'm writing an SQL query auto generation in C++, so I want to make sure I don't miss cases, when I have to add quotes.

Answer 1

Single quotes (') denote textual data, as you noted (e.g., 'Mike' in your example). Numeric data (e.g., 3 in your example), object (table, column, etc) names and syntactic elements (e.g., update, set, where) should not be wrapped in quotes.

Answer 2

The single quote is the delimiter for the string. It lets the parser know where the string starts and where it ends as well as that is is a string. You will find that sometimes you get away with a double quote too.

The only way to be certain you don't miss any cases would be to escape the input, otherwise this will be vulnerable to abuse when somehow a single quote ends up in in the text.