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.

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