I have been preaching both to my co-workers and here on SO concerning the goodness of utilizing parameters in SQL queries, particularly in .Internet programs. I have even gone to date regarding promise them as giving immunity against SQL injection attacks.
But I am beginning to question if that is true. What are the known SQL injection attacks that'll be successfull against a parameterized query? Are you able to for instance send a string that triggers a buffer overflow around the server?
You will find obviously other factors to create to make sure that an internet application is protected (like cleaning user input and all sorts of that stuff) but now i'm considering SQL injections. I am especially thinking about attacks against MsSQL 2005 and 2008 being that they are my primary databases, but all databases are interesting.
Edit: To clarify the reason by parameters and parameterized queries. By utilizing parameters I am talking about using "variables" rather than building the sql query inside a string.
So rather than carrying this out:
Choose * FROM Table WHERE Title = 'a name'
We all do this:
Choose * FROM Table WHERE Title = @Title
after which set the need for the @Title parameter around the query / command object.
Placeholders are sufficient to avoid injections. You'll probably still most probably to buffer overflows, but that's a totally different flavor of attack from an SQL injection (the attack vector wouldn't be SQL syntax but binary). Because the parameters passed all will be steered clear of correctly, there is not in whatever way to have an attacker to pass through data that'll be treated like "live" SQL.
You cannot use functions inside placeholders, and also you can't use placeholders as column or table names, since they're steered clear of and cited as string literals.
However, if you are using parameters included in a string concatenation within your dynamic query, you're still susceptible to injection, since your strings won't be steered clear of and can be literal. Using other forms for parameters (for example integer) is protected.
Nevertheless, if you are using use input to create the need for something similar to
security_level, then someone could simply make themselves managers in your body and also have a free-for-all. But that is just fundamental input validation, and it has nothing related to SQL injection.
any sql parameter of string type (varchar, nvarchar, etc) that's accustomed to create a dynamic totally still vulnerable
otherwise the parameter type conversion (e.g. to int, decimal, date, etc.) should eliminate any make an effort to inject sql through the parameter
EDIT: a good example, where parameter @p1 will probably be a table title
create procedure dbo.uspBeAfraidBeVeryAfraid ( @p1 varchar(64) ) AS SET NOCOUNT ON declare @sql varchar(512) set @sql = 'select * from ' + @p1 professional(@sql) GO
If @p1 is selected from the drop-lower list it's a potential sql-injection attack vector
If @p1 is developed programmatically w/out ale the consumer to intervene then its not really a potential sql-injection attack vector
There appears to become some confusion within this thread about the phrase a "parameterised query".
- SQL like a saved proc that accepts parameters.
- SQL that's known as while using DBMS Parameters collection.
Because of the former definition, most of the links show working attacks.
However the "normal" definition may be the latter one. Considering the fact that definition, I'm not sure associated with a SQL injection attack which will work. That does not imply that there is not one, however i haven't yet view it.
In the comments, I am not indicating myself clearly enough, so here's a good example which will hopefully be clearer:
This method is available to SQL injection
professional dbo.MyStoredProc 'DodgyText'
This method is not available to SQL injection
using (SqlCommand cmd = new SqlCommand("dbo.MyStoredProc", testConnection))
This is a dated article, but nonetheless valid: