How to escape double quote in string in SQLite query?

Question

I am attempting to query based on a string in SQLite, but the problem I have run into is when a string contains a double quote ("). I have yet to figure out how to escape it.

I am using DB Browser for SQLite. The string is 3" Nail. It was inserted programmatically, but when I attempt to query it programmatically or through the SQLite Browser, it returns zero rows, notes my query, but has the string literal as '3&quot Nail' (with a semicolon after the 'quot' part, but I cannot put it here, or it translates it to "). I suspect this is my problem; that it is a different character inserted than what I am attempting to query, but I do not know how to determine this.

For the record, I have tried escaping using single quote (') and backslash (\), so I have tried the following queries:

 1. SELECT * FROM TABLE WHERE NAME = '3" Nail';
 2. SELECT * FROM TABLE WHERE NAME = '3\" Nail';
 3. SELECT * FROM TABLE WHERE NAME = '3'" Nail';
 4. SELECT * FROM TABLE WHERE NAME = '3'''' Nail';
 5. SELECT * FROM TABLE WHERE NAME = '3\'\' Nail';

Any help would be most appreciated!

-----UPDATE-----

All. I apologize. Call off the hunt. I have figured out the issue.

In order to simplify the question, I showed a simple query, but the query I was actually trying was more complicated. It involved a JOIN, and it was failing because the function to insert the data was off. It first inserted into one table, then queried that table for the ID of the item just inserted and used that ID in the insert into the second table. That query used escaped double quotes (") around the name instead of single quotes ('). Apparently this worked fine for normal situations, but choked on the situation with the double quotes, so the query for the ID returned zero rows and, therefore, did not perform the secondary insert.

Since my query was using a JOIN of these tables, and there was no data in the second table for the name, it was completing successfully, but returning zero rows. I had checked the first table to ensure the data I needed from it was there, but it had not even crossed my mind to check the other table, too.

I corrected the insert function, and now the query works as it should.

I should have done a better job investigating before posting a question. I apologize, but sincerely thanks for all your help.

Tessellation

Two of them in a row? – Truffle

@Truffle there is no need to escape a double quote. – Willwilla

@Joseph 1st query doesn't work? what is actually stored in the table? " or "? – Willwilla

@Truffle I have tried using two double quotes, but that does not work either. It says the same thing as above, but with '3&quot ;&quot ; Nail'. – Tessellation

@GurwinderSingh As best I can tell, " is stored in the table. When I use the DataBrowser tool of DB Browser for SQLite, that is what I see. Is there a way to determine which it truly is? – Tessellation

You should look how you inserted the data, perhaps you simply inserted with &quot ; - possible if input was e.g. encoded in URL. Just run SELECT field, LENGTH(field) and you'll see if you see 7 for '3" Nail" or more. – Cotenant

Show the output of SELECT hex(name) FROM MyTable WHERE name LIKE '3%Nail'; – Photogram

@MarcinZukowski Great idea! 7 is returned for it, so that would confirm it is stored as a " and not ". – Tessellation

@Photogram Here is the hex output: 3322204E61696C – Tessellation

All. See the update I just posted. Since this is now an invalid question, I am going to delete it, but I wanted to give you each a chance to see the problem before I do. – Tessellation

It happens. But next time, for SQL questions, it's a good idea to prepare a reproducible case. sqlfiddle (sqlfiddle.com) makes it easy :) – Cotenant

Answer 1

The SQL standard specifies that single-quotes in strings are escaped by putting two single quotes in a row. SQL works like the Pascal programming language in the regard. SQLite follows this standard.

It works for double quotes also.

INSERT INTO TABLENAME VALUES("WHAT""S THAT")
INSERT INTO TABLENAME VALUES("WHAT''S THAT")

read this How do I use a string literal that contains an embedded single-quote (') character

Awful

This solution worked for me. Thank you. In my use case, I couldn't used placeholders one "should" use instead. The doubling of single/double quote works fine. – Peerage

Answer 2

Between '', you can use double quotes without escaped as shown below so '3" Nail' is evaluated to 3" Nail. *My answer explains it more:

                                 ↓       ↓           
SELECT * FROM TABLE WHERE NAME = '3" Nail';

In addition, you cannot use double quotes to surround the value as shown below because there is error:

                                 ↓ Error  ↓ 
SELECT * FROM TABLE WHERE NAME = "3"" Nail";

Devanagari