0

I want to escape single quote when a user enters an input text such as "Sam's project deliverable" in a form entry developed in C# (.NET). Database is ORACLE 10g.

I came across Quote delimiter feature offered from ORACLE 10g http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_escape_special_characters_when_writing_SQL_queries.3F but I am not sure if using such a quote delimiter q'[ some text with single quote]' as part of a SELECT statement would prevent SQL Injection attacks?

Example usage of quote delimiter q'[ text with single quote ]':

SQL> SELECT q'[Frank's Oracle site]' AS text FROM DUAL;
 TEXT
 -------------------
 Frank's Oracle site

 SQL> SELECT q'[A 'quoted' word.]' AS text FROM DUAL;
 TEXT
 ----------------
 A 'quoted' word.

 SQL> SELECT q'[A ''double quoted'' word.]' AS text FROM DUAL;
 TEXT
 -------------------------
 A ''double quoted'' word.
sunskin
  • 1,620
  • 3
  • 25
  • 49
  • 4
    and your problem is...? – Marc B Apr 23 '13 at 15:49
  • 2
    http://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection will help you out. – penguat Apr 23 '13 at 15:51
  • @MarcB: Sorry Marc there was some issue while posting the question. Now my post should explain my problem/question – sunskin Apr 23 '13 at 15:57
  • 1
    It won't prevent injection. It will probably slow down half-hearted attempts because the attackers will assume the end delimiter is `'` and get nowhere. But a pro won't stop there and with brute force they can break the quote literal easily enough. – Ed Gibbs Apr 23 '13 at 16:05
  • 2
    No - this will not prevent injection attacks. The ONLY thing that will prevent injection attacks is to NEVER build dynamic SQL statements with user-entered data directly in the SQL text; i.e. ALWAYS access the user-entered data through parameters markers. ALL-WAYS!!! – Bob Jarvis - Слава Україні Apr 23 '13 at 16:12
  • @BobJarvis: Examples please? – sunskin Apr 23 '13 at 16:19
  • [Here](http://www.sqlfiddle.com/#!4/d3bba/1) is an example for sql injection with `--` instead of `'` – A.B.Cade Apr 23 '13 at 16:19

2 Answers2

5

If your query looks anything like this...

query = "SELECT * FROM Users WHERE name = q'[" + <what the user entered> + "]'";

... and the user enters something like this...

abc]';<harmful statement>;SELECT * FROM DUAL WHERE '1'=q'[1

... they'll have a successful injection.

It won't matter which delimiters you use with the q - a determined attacker will try them all. Prepared statements are your best defense.

Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
0

Try replacing all single quotes with 2 single quotes before executing the SQL. i.e. replace ' with '' before executing the SQL command.

In C#:

string sql = originalString.Replace("'", "''");
Martyn Atkins
  • 34
  • 3
  • 4
    it's much safer to use parameterised queries, if you can. – penguat Apr 23 '13 at 15:55
  • @Martyn: Thanks for your answer. I am just trying to explore the option of using quote delimiter q'[ ]' and how safe it is? And if it prevents injection? – sunskin Apr 23 '13 at 16:00
  • @penguat: thanks for the link and suggestion. I will look into it. – sunskin Apr 23 '13 at 16:12
  • 1
    I understand. This article probably helps more than I can - http://stackoverflow.com/questions/3544484/how-to-do-sql-injection-on-oracle – Martyn Atkins Apr 23 '13 at 16:13
  • @penguat: does string sql = originalString.Replace("'", "''"); comes under parameterised query? (I am a beginner please apologize for my lack of understanding of a parameterised query) – sunskin Apr 23 '13 at 16:14
  • 1
    No - I believe parameterised queries send the query with placeholders to the database, and then the user input to fill the placeholders. try searching "_c# oracle parameterised query example_" – penguat Apr 24 '13 at 10:49