CODEWITHSUNDEEP
sqloracle-database
Loren Ramly
Loren Ramly

Reputation: 1111

Inserting a string with double quotes into a table

I'm using Oracle 10g and I'm having a problem inserting a string with double quotes into a table. This is my statement

INSERT INTO USERS (ID, NAME, USERNAME) VALUES (NULL, "tes", "hello");

The query above fails with the error "Oracle column not allowed here".

If I change double quotes to single quotes, as below the statement is successful.

INSERT INTO USERS (ID, NAME, USERNAME) VALUES (NULL, 'tes', 'hello');

But, I want to insert the double quotes into the table.

Is it possible to have double quote in strings in an insert statement? I don't want to use REPLACE() because my query is automatically generated from an array.

Upvotes: 15

Views: 145784

Answers (3)

Alen Oblak
Alen Oblak

Reputation: 3325

It is possible. In Oracle, you quote string literals using single quotes.

If you want to insert test into the database then you must quote that as 'test'.

INSERT INTO USERS (NAME) VALUES ('test');

If you want to insert "test" into the database then you must quote that as '"test"'.

INSERT INTO USERS (NAME) VALUES ('"test"');

Upvotes: 12

Ben
Ben

Reputation: 52923

A double quote is used to denote a quoted identifier, i.e. an object name that does not solely consist of alpha-numeric characters, $ and #. As an aside, it's recommended that you do not use quoted identifiers. This is the reason for your original ORA-00984 error. Oracle is assuming that "tes" is a column, not a string, and you can't use a column name in the VALUES clause of an INSERT statement, as explained in the error message.

In order to insert the string "tes" into a table you need to ensure that it is quoted correctly:

Character literals are enclosed in single quotation marks so that the database can distinguish them from schema object names.

Any character can be part of a string so in order to insert a double quote into a table you need to enclose it within single quotes.

insert into users (id, name, username) 
values (null, '"tes"', '"hello"');

Here's a SQL Fiddle to demonstrate.

One additional thing to note. You state that this query is automatically generated, which means you may be vulnerable to SQL injection. I would highly recommend reading about bind variables in Guarding Against SQL Injection.

Upvotes: 18

TechDo
TechDo

Reputation: 18659

Try wrapping the values inside single quotes.

INSERT INTO USERS (ID, NAME, USERNAME) VALUES (NULL, '"tes"', '"hello"');

Upvotes: 6

Related Questions