Hello,
I wanted to ask if anyone knows of a way to prevent injection in an SQL SERVER 2005. I mean, is there any way to do all the blocking in the server and not have to escape each special character one-by-one?
For example, in PHP I used mysql_escape_string and automatically the string was OK to send to the database... Is there something similar in SQL Server? Thank you

When you use mysql_escape_string in PHP that isn't blocking a SQL Injection attack at the database side, thats blocking it at the code side. As for blocking it in MSSQL, do your work in Stored Procedures, this will prevent a SQL Injection attack. If you choose to not use Stored Procedures, then you have to to data validation before sending it to the database.

It's not the database, it's the client language. Even if that client is the web server. The best defense against SQL injection is bind, bind, bind.

For a C# / SQL client example, do NOT do this:


Rather, do this:


Not only does this protect you from someone passing something like "x'; select @@version;", but it makes your database more responsive because it doesn't have to keep parsing different queries for the same thing.

Stored procedures are even more responsive as they're pre-compiled. When you use inline SQL like you're suggesting, it has to be compiled on the server before it can be executed.

When using SQL Server why would you want to use inline SQL to begin with? I can understand inline SQL when using Access as Access doesn't support stored procedures, but in a database which runs better with them why wouldn't you use them?

The best thing to do to avoid SQL Injection is using parameterized queries instead of concatenating strings together.

The other big issue you might want to look into is cross-site scripting, which allows a user to type javascript into your input fields and hack your site that way.

Heres some information on that: http://msdn2.microsoft.com/en-us/library/ms998274.aspx

I would have to disagree with that statement. Stored procedures really are the best way to prevent a SQL Injection attack, plus they're pre-compiled so they execute faster than inline SQL. Also in my opinion they're more secure because theres another level of separation beings that they aren't stored on the web server.

Using stored procedures have the same exact effect as using a parameterized query. It is *impossible* to be SQL injected when using a parameterized query.

and as for stored procedures being compiled, here is a quote from Microsoft's Books Online



I know its a common myth, but call me the myth buster =). Dynamic queries execution plan is cached.

And for the security, yes there is a level of separation (being that the stored procedures are on your SQL server) but if someone gained access to your web server, what makes you think your SQL Server is safe? At least if you have proper encryption in your config they wont gain access to the server via a connection string, but I think having the queries compiled into a binary is just as safe.

I think http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx covers the topic quite well.

But again, this is one of those arguments like 'linux vs windows', 'vim vs emacs', 'chocolate vs vanilla', no one can win it, its all a matter of preference, I'm just here to inform you of misconceptions about stored procedures, and that the original poster shouldn't make his decision based on myths.

Let me break the stalemate:

Linux is basicaly for free and fundamentaly opensource, and should therefore be preferable over the extorting, patent abusing monopolist created/'borrowed' Xwindows derivative DOS 9.0 to any sane person.

To use emacs you need to understand Lisp. Since you need to have a knot in your brain for that, vim is the best alternative to most, allthough a simpler editor would be highly preferable to most.

I am allergic to a group of fats that are amongst other things in chocolate, so not much of a choice there.

To prevent sql injection you can both use stored procedures and parametrized queries. It just depends how much work you want to spend doing all that, especially creating many stored procedures just to pass some parameters is a lot of work. THAT is a matter of taste. The other things as mentioned above are not realy.

And once again I would have to disagree. It has been proven over and over again that Stored Procedures execute faster than inline SQL, because they are pre-compiled code, inline SQL has to be interpreted by the CLR, then send it to the database which in turns has to compile and run the code.

Why take the time to write inline code, that runs slower, when it takes no more time to create a stored procedure, since it is just the code you would have used for the inline SQL.




I never said that parameterized SQL was insecure, what I said is it has been shown over and over again that it operates much more efficiently than inline SQL. If you're going to do something, doesn't it make sense to do it the most efficient way possible the first time?

There is a way to get something to work, and then theres the right/efficient way to do something, so why not take the little extra time to do something the efficient way instead of the way you think is easier. The easy way isn't always the right way

I don't believe this is the case. What's interpreted in the CLR is the object that makes the database call. A DbCommand for a stored procedure actually as a little more wrapper code added, actually. The final call always looks the same to the database.

The database sees either just SQL or SQL with bind parameters to follow.. What the database does is hashes the SQL and sees if it has anything precompiled in cache. If it does, it uses that.

Here's where a stored procedure can be potentially faster. If it hasn't changed at all since the last time SQL Server used it, it uses a compile that's been stored. There are many things that can count as a change here, even reindexing a dependent table. Chances are, with regular maintenance, a stored procedure will be a recompile every few days.

So, you call a stored procedure which is either loaded from a precompile and cached or loaded, compiled, cached, with the resulting compile stored fresh. You call just inline SQL and the code is parsed, compile and cached. If you make the exact same call again, it uses the code in the cache.

So, why is parameterized inline SQL just as good? Because the SQL is cached with knowledge of scalars being part of the call. In effect, in terms of cache, it becomes a stored procedure for the duration it's in cache.

Hope this helps.

You guys are all correct in your points but you have to realize that you are all briefly touching on the differences while making your arguments.

1) Parameterized SQL is more secure (that isn't in dispute) and yes the query is held in memory waiting for scalars. It is good for dynamic sql generation and better than straight concatenation.

2) However, Pyscho is absolutely correct in that SQL procedures are precompiled and held in memory to speed up operations server side. They are also very secure in that they are executed from the server itself where permissions can be controlled and code can't be injected into it.

3) What you guys are failing to realize is that a secure system can use and should use both. The thing about stored procedures is that they are often written as broad sweeping functions that would normally take a bit of time to execute or when you need specific permissions on what is ran against the data... it is also good for views. You then use the parameterized queries for more sharp direct edits or small subsets of data.

So for instance, if you are going to pull thousands of records, you might want to use a stored procedure. If you are going to edit a single record you might want to use the parameterized query... especially if it takes a different parameter (like an ID) each time. You wouldn't write a stored procedure to pull or edit just one record at a time, it is a waste of time to program such a procedure... especially if it takes different parameters almost every call because of what baavgai was eluding to, it is forced to recompile each time for small changes.

4) Also regarding the CLR, baavgai was correct that the database doesn't see anything about where the query is coming from or what is causing it. This is because it is routed through a driver... the one you specify in your connection string or the one you setup on the server itself through a DSN. All it sees is a query and it should be like that, because you may have different sources querying at the same time... one from .NET, one from the web, one from C++, one from a local web service.

In short you should not be doing all one or all the other. You should be utilizing both. I have used both in several systems together in perfect harmony and with great success.

As for answering the original post, there is no dedicated function for escaping that I know of. However many people do write their own for handling escaping as a preparation of sql statements. But you really should use both stored procedures and bound parameters whenever possible since they are both very secure methods with different scopes (as outlined above).

I hope I have clarified some things.

This post has been edited by Martyr2: 27 Oct, 2007 - 04:09 PM

Can you guys please read my previous post again? I'll even quote myself since you seemed to miss it the first time.


That quote is directly from Microsoft's books online. If you guys still want to believe they are pre-compiled, even though Microsoft says they are not, I can't help ya =)