CODEWITHSUNDEEP
oracle-database
Gosforth
Gosforth

Reputation: 51

How to insert special characters into oracle table?

How to insert special characters into Oracle table? I found example where this is evident what this 'special character' will be and is escaped for instance with another comma. In my code I do not know what this character will be. Can be &, ", ', ....

Is there any escape function in Oracle or I have to do write piece of code? My code is:

l_sql := 'INSERT INTO similar (product_id, product_name) VALUES (' || l_product_id || ',''' || l_desc || ''')';

Regards

Upvotes: 0

Views: 410

Answers (1)

pmdba
pmdba

Reputation: 7043

It appears that you are using EXECUTE IMMEDIATE to run this. Is there a specific reason you would do that, and not just put a normal INSERT command in your procedure when you already have your values stored in variables (l_product_id and l_desc)?

The correct approach would be to issue the insert command directly and avoid concatenating strings entirely. Then it doesn't matter what the content of your variables is, and you're not leaving yourself potentially vulnerable to SQL injection attacks.

INSERT INTO similar (product_id, product_name) VALUES (l_product_id, l_desc);

I can't imagine off the top of my head why you would, but if you absolutely must use EXECUTE IMMEDIATE, then do it this way using bind variables:

l_sql := 'INSERT INTO similar (product_id, product_name) VALUES ( :x, :y )';
EXECUTE IMMEDIATE l_sql USING l_product_id, l_desc;

Either way your "special character" problem should go away.

Upvotes: 2

Related Questions