Avoiding SQL injection without parameters

We are having another discussion here at work about using parametrized sql queries in our code. We have two sides in the discussion: Me and some others that say we should always use parameters to safeguard against sql injections and the other guys that don't think it is necessary. Instead they want to replace single apostrophes with two apostrophes in all strings to avoid sql injections. Our databases are all running Sql Server 2005 or 2008 and our code base is running on .NET framework 2.0.

Let me give you a simple example in C#:

I want us to use this:

string sql = "SELECT * FROM Users WHERE Name=@name";
SqlCommand getUser = new SqlCommand(sql, connection);
getUser.Parameters.AddWithValue("@name", userName);
//... blabla - do something here, this is safe

While the other guys want to do this:

string sql = "SELECT * FROM Users WHERE Name=" + SafeDBString(name);
SqlCommand getUser = new SqlCommand(sql, connection);
//... blabla - are we safe now?

Where the SafeDBString function is defined as follows:

string SafeDBString(string inputValue)
{
return "'" + inputValue.Replace("'", "''") + "'";
}

Now, as long as we use SafeDBString on all string values in our queries we should be safe. Right?

There are two reasons to use the SafeDBString function. First, it is the way it has been done since the stone ages, and second, it is easier to debug the sql statements since you see the excact query that is run on the database.

So then. My question is whether it really is enough to use the SafeDBString function to avoid sql injection attacks. I have been trying to find examples of code that breaks this safety measure, but I can't find any examples of it.

Is there anybody out there that can break this? How would you do it?

**EDIT:**
To summarize the replies so far:

- Nobody has found a way to get around the SafeDBString on Sql Server 2005 or 2008 yet. That is good, I think?
- Several replies pointed out that you get a performance gain when using parametrized queries. The reason is that the query plans can be reused.
- We also agree that using parametrized queries give more readable code that is easier to maintain
- Further it is easier to always use parameters than to use various versions of SafeDBString, string to number conversions and string to date conversions.
- Using parameters you get automatic type conversion, something that is especially useful when we are working with dates or decimal numbers.
- And finally: [Don't try to do security yourself][1] as JulianR wrote. The database vendors spend lots of time and money on security. There is no way we can do better and no reason we should try to do their job.

So while nobody was able to break the simple security of the SafeDBString function I got lots of other good arguments. Thanks!


[1]:

[To see links please register here]

I'd use stored procedures or functions for everything, so the question wouldn't arise.

Where I have to put SQL into code, I use parameters, which is the only thing that makes sense. Remind the dissenters that there are hackers smarter than they are, and with better incentive to break the code that's trying to outsmart them. Using parameters, it's simply not possible, and it's not like it's difficult.

I have used both approaches to avoid SQL injection attacks and definitely prefer parametrized queries. When I have used concatenated queries I have used a library function to escape the variables (like mysql_real_escape_string) and wouldn't be confident I have covered everything in a proprietary implementation (as it seems you are too).

And then somebody goes and uses " instead of '. Parameters are, IMO, the only safe way to go.

It also avoids a lot of i18n issues with dates/numbers; what date is 01/02/03? How much is 123,456? Do your servers (app-server and db-server) agree with each-other?

If the risk factor isn't convincing to them, how about performance? The RDBMS can re-use the query plan if you use parameters, helping performance. It can't do this with just the string.

With parameterised queries you get more than protection against sql injection. You also get better execution plan caching potential. If you use the sql server query profiler you can still see the 'exact sql that is run on the database' so you're not really losing anything in terms of debugging your sql statements either.

You aren't able to easily do any type checking of the user input without using parameters.

If you use the SQLCommand and SQLParameter classes to make you're DB calls, you can still see the SQL query that's being executed. Look at the SQLCommand's CommandText property.

I'm always a litle suspect of the roll-your-own approach to preventing SQL injection when parameterized queries are so easy to use. Second, just because "it's always been done that way" doesn't mean it's the right way to do it.

From the very short time I've had to investigate SQL injection problems, I can see that making a value 'safe' also means that you're shutting the door to situations where you might actually want apostrophes in your data - what about someone's name, eg O'Reilly.

That leaves parameters and stored procedures.

And yes, you should always try to implement code in the best way you know now - not just how its always been done.

This is only safe if you're guaranteed that you're going to pass in a string.

What if you're not passing in a string at some point? What if you pass just a number?

[To see links please register here]

DATABASE DB

Would ultimately become:

SELECT * FROM DB WHERE Id = 7;DROP DATABASE DB

Here are a couple of articles that you might find helpful in convincing your co-workers.

[To see links please register here]

[To see links please register here]

Personally I prefer to never allow any dynamic code to touch my database, requiring all contact to be through sps (and not one which use dynamic SQl). This means nothing excpt what I have given users permission to do can be done and that internal users (except the very few with production access for admin purposes) cannot directly access my tables and create havoc, steal data or commit fraud. If you run a financial application, this is the safest way to go.

Agree hugely on the security issues.
Another reason to use parameters is for efficiency.

Databases will always compile your query and cache it, then re-use the cached query (which is obviously faster for subsequent requests).
If you use parameters then even if you use different parameters the database will re-use your cached query as it matches based on the SQL string before binding the parameters.

If however you don't bind parameters then the SQL string changes on every request (that has different parameters) and it will never match what's in your cache.