Inserting a double-quoted row in a table

Question

I am using Oracle 10g and I had a problem inserting a double quoted string into a table. This is my statement.

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

The above request with the error "Oracle column is not allowed here."

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

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

But I want to insert double quotes in the table.

Is it possible to have a double quote in strings in an insert statement? I do not want to use REPLACE () because my request is automatically generated from an array.

Answer 1

The double quotation mark is used to indicate the identifier being quoted , that is, the name of the object, which consists not only of alphanumeric characters, $ and # . As deferred, it is recommended not to use quoted identifiers. This is the cause of your original ORA-00984 error. Oracle assumes "tes" is a column, not a row, and you cannot use the column name in the VALUES clause of the INSERT statement, as explained in the error message .

To insert the string "tes" into the table, you must ensure that it is correctly specified:

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

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

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

Here's a SQL Fiddle to demonstrate.

---

One more remark. You indicate that this query is generated automatically, which means that you may be vulnerable to SQL injection. I would highly recommend reading about binding variables in Guarding Against SQL Injection .

Answer 2

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

If you want to insert test into the database, you must specify this as 'test' .

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

If you want to insert "test" into the database, you must specify this as '"test"' .

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

Answer 3

Try wrapping the values ​​inside single quotes.

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