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.

Improve this question
4
  • 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. Commented Oct 3, 2015 at 19:54
  • @Charleh does it mean that "id='3'" will converted to "id=3". Is that what you mean? Commented Oct 3, 2015 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. Commented Oct 3, 2015 at 20:05
  • Since I am looking for the same information, I had the impression, this question is not yer properly answered. Other cases where you need to use single quotations is for date and time values. If I find a complete list of types (I'm looking for Postgres), I will come back to this discussion. Commented May 24, 2024 at 8:57

2 Answers 2

Reset to default
6

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I think, the question was rather in which cases (data types) is necessary to use single quotations for values. For instance text, charvar, date (time), etc.
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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.