Standard Function for Escaping Strings in SQL

Question

I'm writing a program in .NET2.0 and I need to escape the inputs before using them. Unfortunately the standard parameter method system does not fully work in the system I'm using. Using the ODBCCommand class I cannot place a ? parameter in the select part of the statement (which is required for the little bit of trickiness I'm doing) without getting an error, so I need to manually escape strings that may or may not contain a single quote ('). Any suggestions?

Edit- Example SQL:

As I would like it:

INSERT INTO TABLE_A (COLUMN_A, COLUMN_B)
SELECT (?, COLUMN_C)
FROM TABLE_B
WHERE COLUMN_D = ?

As it is:

INSERT INTO TABLE_A (COLUMN_A, COLUMN_B)
SELECT ('INPUT_VALUE_HERE', COLUMN_C)
FROM TABLE_B
WHERE COLUMN_D = ?

Edit: Sybase ASE is the DB driver, through ODBC

Can you give an example of the SELECT you are trying to execute? — Steven
– Steven, Commented Apr 23, 2009 at 12:46
If this is column name or table name etc. do not forget that attacker doesn't need a single quote to do injection. Otherwise ? supposed to work, isn't it? — dr. evil
– dr. evil, Commented Apr 23, 2009 at 13:07
Also what database are you using? Not all databases have the same set of escape characters and depending on where in the SELECT statement you are you may need different escape characters. — Steven
– Steven, Commented Apr 23, 2009 at 13:18
The example provided should work with a parameter. What is the database and what is the error? — Steven
– Steven, Commented Apr 23, 2009 at 13:41

Patrick McDonald · Accepted Answer · 2009-04-23 12:46:32Z

3

Dim s As String = "Michael O'Flatley"
Dim escapedString as String = s.Replace("'", "''")

answered Apr 23, 2009 at 12:46

Patrick McDonald

65.7k14 gold badges114 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Patrick McDonald Over a year ago

I can't speak for Sybase ASE, but in SQL Server and Oracle you would only need to escape % (and _) if it is part of a like clause

Patrick McDonald · Accepted Answer · 2009-04-23 14:21:33Z

3

You can parse your string parameters with this extension function

public static string SqlEncode(string str)
{
    if (str == null) return String.Empty;
    return str.Replace("'","''");
}

edited Apr 23, 2009 at 14:21

Patrick McDonald

65.7k14 gold badges114 silver badges129 bronze badges

answered Apr 23, 2009 at 12:54

Soni Ali

19.1k17 gold badges47 silver badges53 bronze badges

4 Comments

Patrick McDonald Over a year ago

to be an extension function should it be public static string SqlEncode(this string str)?

C. Ross Over a year ago

Good idea, but if you look you'll see this .NET2.0 (no extension methods).

Soni Ali Over a year ago

I did not make it an extension because he said .NET 2.0.

Patrick McDonald Over a year ago

Sorry Soni, you're right, I read extension in your answer and thought you meant to write an extension methos

Collectives™ on Stack Overflow

Standard Function for Escaping Strings in SQL

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related