So I was playing with my MS SQL Server 2008 app to see how good it is protected against SQL injections. The app lets users to create views in the database.

Now consider the following:

create view dbo.[]]; drop database foo--] as select 1 as [hi!]

This creates a view with a name of ]; drop database foo--. It is valid and you can select from it (returns the 开发者_开发百科number 1, obviously).

Strange thing #1:

In SQL Management Studio, the query SELECT [hi!] FROM [dbo].[]]; drop database foo--] is red-underlined as incorrect, claiming that the object name is not valid. Nevertheless, it executes and returns the 1.

Strange thing #2:

Call to OBJECT_ID(']; drop database foo--') yields NULL (which means the object does not exist), but the following query returns information about the view properly:

select * from sys.objects where name = ']; drop database foo--';

Are those bugs or am I missing a point?

---

You're missing the point. SQL Server can't protect itself against SQL injection - if somebody has direct access to your database then you've already been pwned. It's your application that needs to protect against SQL injection by parameterizing queries, and preventing these kinds of statements from ever making it to the database.

---

• 1: that only means the intellisense parser is not up to par witht the finer details of SQL syntax. While it may be an intellisense bug, it is not an injection vector.

• 2: object_id() accepts multipart names, so it needs the name in quotes if ambiguous: select object_id('[]]; drop database foo--]')

---

That's like using your key to get into your car and then saying "hey there's a security hole, I'm allowed to steal the radio"

---

It seems the problem is that you are yourself causing SQL injection by accepting user input and using it as SQL statement text.

The fact that you "properly escaped" the ] (by substituting with ]]) really doesn't matter - it's you allowing the user input to be used as anything else but a value by definition means you allow SQL injection.