5

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.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
rightaway717
  • 2,631
  • 3
  • 29
  • 43
  • You need to wrap strings in quotes. Numerics/decimals etc should not be in quotes but oftentimes you can put them in quotes and they will be implicity converted - it depends what version of SQL you are using. – Charleh Oct 03 '15 at 19:54
  • @Charleh does it mean that "id='3'" will converted to "id=3". Is that what you mean? – rightaway717 Oct 03 '15 at 19:59
  • @rightaway717: Yes, in many SQL DBMS, if the type of column `id` is INTEGER but you pass `'3'` as a value to be compared, that may be accepted and the conversion done at some point in the statement preparation or execution. Other DBMS will simply give you a mismatched type error from the start. – Jonathan Leffler Oct 03 '15 at 20:05

2 Answers2

7

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.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
1

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.

kcrk
  • 164
  • 1
  • 8