Hey I'm using oracle DB with Iron Python and I'm having trouble with strings that contains the char " ' " like in Mc'donalds. (I know it is looking for the closing ' ) The string is accepted from the user input and I want to add it to my DB as it is, meaning without omitting or changing any character. How can I do it?
Asked
Active
Viewed 5,016 times
3
-
3If IronPython has query parameters, that should solve your problem. – Dan Bracuk Apr 05 '14 at 16:50
-
2All explained in the manual (including examples): http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements003.htm#i42617 – Apr 05 '14 at 17:35
2 Answers
1
Try using the "q" (quote) function:
INSERT INTO restaurantTable (name)
VALUES (q'[O'Reilly and Conway's Irish Pub]');
You can also double-up the single apostrophes (O''Reilly and Conway''s Irish Pub). But in your case you'd have to parse them out, so either using the quote function or query parameters would work the best.
For more information: Q-quote operator introduced in Oracle 10g
Aaron
- 55,518
- 11
- 116
- 132
-
1The string is "accepted from user input" - using your code would introduce a SQL injection vulnerability. – Jeffrey Kemp Apr 07 '14 at 05:30
-
By virtue of the tags, the OP wanted to know how to do it in SQL. Also I did recommend using query parameters for that exact reason. – Aaron Apr 07 '14 at 06:04
-
If a stranger asks you how to shoot themselves in the foot, you don't start by teaching them techniques for holding the gun. i.e. that comes later after they've explained why shooting themselves in the foot is the only practical way of solving their problems. :) – Jeffrey Kemp Apr 07 '14 at 06:12
0
Taken from PL/SQL, how to escape single quote in a string?
You can do it either using Q quoting like
q'[insert into MY_TBL (Col) values('ER0002')]';
OR you can use two quotes to denote a single quote
'insert into MY_TBL (Col) values(''ER0002'')';
