4

I need a complete list of characters that should be escaped in sql string parameters to prevent exceptions. I assume that I need to replace all the offending characters with the escaped version before I pass it to my ObjectDataSource filter parameter.

Slim
  • 5,635
  • 7
  • 31
  • 30
  • 5
    Why aren't you using parameterized SQL with bind variables? If you use bind variables, you never need to escape anything. – S.Lott Jul 09 '09 at 17:52
  • I decided not to use a parameterized SQL with bind variables because I have a variable number of parameters. – Slim Jul 09 '09 at 17:56
  • 1
    Is there not a Parameters collection? It's great for a variable number of parameters. – John Saunders Jul 09 '09 at 18:00
  • 2
    So you're rolling your own, risking SQL injection, etc? – gbn Jul 09 '09 at 18:01
  • I was looking for a simple solution – Slim Jul 09 '09 at 18:05
  • You can still create your SQL based on the amount of parameters, and still get the benefit of the use of parameters – Jhonny D. Cano -Leftware- Jul 09 '09 at 18:14
  • hi, just post your method how you create your sql statement, i'm sure people will show you how to do it correct and clean and without any string replacements. – nWorx Jul 09 '09 at 18:16
  • 2
    Check out this post: http://stackoverflow.com/questions/1105643/help-production-db-was-sql-injected That's what you get when you "look for a simple solution" ........ – marc_s Jul 09 '09 at 18:37
  • I would like someone to explain how you do a SQL injection on a ObjectDataSource filter. – Slim Jul 10 '09 at 16:42

4 Answers4

3

No, the ObjectDataSource will handle all the escaping for you. Any parametrized query will also require no escaping.

Andrew Hare
  • 344,730
  • 71
  • 640
  • 635
  • This is not a yes or no question bro – Slim Jul 09 '09 at 17:57
  • It is to a certain extent. You said "I assume that I need to replace all the offending characters" and this is not true. If you don't need to replace offending characters then you don't need a list of them either. – Andrew Hare Jul 09 '09 at 17:59
  • So you are saying that it is not my assumption? – Slim Jul 09 '09 at 18:01
  • 1
    Well it is not for me to make a ruling on *your* assumption as it is yours alone. What I *am* saying however is that your assumption that you clearly stated is incorrect. – Andrew Hare Jul 09 '09 at 18:12
  • Can you or anyone who up voted your answer show me how to do a parameterized filter with a ObjectDataSource? – Slim Jul 10 '09 at 16:38
1

As others have pointed out, in 99% of the cases where someone thinks they need to ask this question, they are doing it wrong. Parameterization is the way to go. If you really need to escape yourself, try to find out if your DB access library offers a function for this (for example, MySQL has mysql_real_escape_string).

Ringding
  • 2,856
  • 17
  • 10
  • In this case the others are wrong. I am trying to filter not select. It never touches the database so there is no threat of injection attack. Also a ObjectDataSource doesn't offer parameterized filter expressions. It only offers placeholders. – Slim Jul 10 '09 at 16:48
0

Here's a way I used to get rid of apostrophes. You could do the same thing with other offending characters that you run into. (example in VB.Net)

Dim companyFilter = Trim(Me.ddCompany.SelectedValue)

If (Me.ddCompany.SelectedIndex > 0) Then
      filterString += String.Format("LegalName like '{0}'", companyFilter.Replace("'", "''"))
End If

Me.objectDataSource.FilterExpression = filterString

Me.displayGrid.DataBind()
KevinDeus
  • 11,988
  • 20
  • 65
  • 97
0

SQL Books online: Search for String Literals:

String Literals

A string literal consists of zero or more characters surrounded by quotation marks. If a string contains quotation marks, these must be escaped in order for the expression to parse. Any two-byte character except \x0000 is permitted in a string, because the \x0000 character is the null terminator of a string.

Strings can include other characters that require an escape sequence. The following table lists escape sequences for string literals.

\a Alert

\b Backspace

\f Form feed

\n New line

\r Carriage return

\t Horizontal tab

\v Vertical tab

\" Quotation mark

\ Backslash

\xhhhh Unicode character in hexadecimal notation

feihtthief
  • 6,403
  • 6
  • 30
  • 29
  • I am looking for a list of characters that cause a SQL string to malfunction. These all seem to be SQL string friendly. – Slim Jul 10 '09 at 16:53
  • In reference to my last comment. Some of the characters are used for SQL injection attacks but since it is questionable whether or not you can actually achieve one through a ObjectDataSource FilterExpression I'm not worried about them. – Slim Jul 12 '09 at 04:06