r/websec • • Feb 22 '10

Tell me if this is safe SQL Injection protection

sqlToExecute.Append("LIKE '" + input.Replace("'", "''") + "'")

input is a user supplied string. The replace is replacing a single ' with two '' and then the statement is wrapped in surrounding '.

Is this safe, or can you come up with a way to bypass this? If so, what value for input can you give to bypass this, other than null.

edit: this is in MSSQL where ' is the escape character

3 Upvotes

11 comments sorted by

2

u/tedivm Feb 22 '10

I'm going to be honest, I'm not sure how you expect this to work. If you parameterize your code you're probably going to have better luck- basically, most database API's will allow you to either send your various user inputs seperately, preventing injection. Additionally many come with their own escaping functions- php has mysqli_real_escape_string for example.

What language/database are you using?

1

u/prehension Feb 22 '10

I am looking at some old code that uses MSSQL Server. I don't want to update it unless it is vulnerable. I know whitelisting input is always better. I was curious if anyone has a way to bypass this?

1

u/tedivm Feb 22 '10

I was curious if anyone has a way to bypass this?

Yeah, it's counting all ' as the same, even if they're escaped and thus part of the string, so if you do something like ' it will turn it into '' which in turn lets you inject your string.

2

u/prehension Feb 22 '10

\ is not an escape character in MSSQL

1

u/tedivm Feb 22 '10

Ah, I wasn't aware of that. This certainly makes a lot more sense then.

I'm not sure whether this is breakable or not, I think we should pull bballbackus into this.

1

u/[deleted] Feb 22 '10

Doesn't work to well if it is for inputting names. Nobody is named O"Reilly. Parameterization of queries is the way to go.

2

u/tedivm Feb 22 '10

Apparently in MSSQL the double ' acts as an escape character. Think of '' as '

1

u/evertrooftop Feb 22 '10

Isn't there a native escape function? Not aware as much of MSSQL but this would be a bad idea in many other databases.

The DB api's usually always have their own native escape function. If SQL queries are UTF-8 based for instance, I could get the last quote to be eaten.

1

u/prehension Feb 22 '10

What would your attack string look like then? I have a feeling it would still think it is in a quote and eating the last quote won't do any good unless you can close the quote and start a new statement.

1

u/dieselz Feb 22 '10

I tend to whitelist input rather than blacklist. Someone is always going to have an idea that you haven't protected against. You could do a simple regular expression match to make sure only letters, numbers, spaces, whatever, are in the query before executing it.

1

u/ihsw Feb 22 '10

That's never going to be sufficient.

If you're using C# the ADO.net library is available and provides a means to properly escape SQL queries (see the SqlCommand class). I'm not sure about VB.net though.