[ACCEPTED]-Are Parameters really enough to prevent Sql injections?-sql-injection

Placeholders are enough to prevent injections. You 22 might still be open to buffer overflows, but 21 that is a completely different flavor of 20 attack from an SQL injection (the attack 19 vector would not be SQL syntax but binary). Since 18 the parameters passed will all be escaped 17 properly, there isn't any way for an attacker 16 to pass data that will be treated like "live" SQL.

You 15 can't use functions inside placeholders, and 14 you can't use placeholders as column or 13 table names, because they are escaped and 12 quoted as string literals.

However, if you 11 use parameters as part of a string concatenation inside your dynamic query, you 10 are still vulnerable to injection, because 9 your strings will not be escaped but will 8 be literal. Using other types for parameters 7 (such as integer) is safe.

That said, if 6 you're using use input to set the value 5 of something like security_level, then someone could just 4 make themselves administrators in your system 3 and have a free-for-all. But that's just 2 basic input validation, and has nothing 1 to do with SQL injection.

No, there is still risk of SQL injection 27 any time you interpolate unvalidated data 26 into an SQL query.

Query parameters help 25 to avoid this risk by separating literal 24 values from the SQL syntax.

'SELECT * FROM mytable WHERE colname = ?'

That's fine, but 23 there are other purposes of interpolating 22 data into a dynamic SQL query that cannot 21 use query parameters, because it's not an 20 SQL value but instead a table name, column 19 name, expression, or some other syntax.

'SELECT * FROM ' + @tablename + ' WHERE colname IN (' + @comma_list + ')'
' ORDER BY ' + @colname'

It 18 doesn't matter whether you're using stored 17 procedures or executing dynamic SQL queries 16 directly from application code. The risk 15 is still there.

The remedy in these cases 14 is to employ FIEO as needed:

• Filter Input: validate that the 13 data look like legitimate integers, table 12 names, column names, etc. before you interpolate 11 them.

• Escape Output: in this case "output" means putting 10 data into a SQL query. We use functions 9 to transform variables used as string literals 8 in an SQL expression, so that quote marks 7 and other special characters inside the 6 string are escaped. We should also use 5 functions to transform variables that would 4 be used as table names, column names, etc. As 3 for other syntax, like writing whole SQL 2 expressions dynamically, that's a more complex 1 problem.

There seems to be some confusion in this 12 thread about the definition of a "parameterised 11 query".

• SQL such as a stored proc that accepts parameters.
• SQL that is called using the DBMS Parameters collection.

Given the former definition, many 10 of the links show working attacks.

But the 9 "normal" definition is the latter one. Given 8 that definition, I don't know of any SQL 7 injection attack that will work. That doesn't 6 mean that there isn't one, but I have yet 5 to see it.

From the comments, I'm not expressing 4 myself clearly enough, so here's an example 3 that will hopefully be clearer:

This approach 2 is open to SQL injection

exec dbo.MyStoredProc 'DodgyText'

This approach isn't open 1 to SQL injection

using (SqlCommand cmd = new SqlCommand("dbo.MyStoredProc", testConnection))
{
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter newParam = new SqlParameter(paramName, SqlDbType.Varchar);
    newParam.Value = "DodgyText";
    .....
    cmd.Parameters.Add(newParam);
    .....
    cmd.ExecuteNonQuery();
}

any sql parameter of string type (varchar, nvarchar, etc) that 12 is used to construct a dynamic query is 11 still vulnerable

otherwise the parameter 10 type conversion (e.g. to int, decimal, date, etc.) should 9 eliminate any attempt to inject sql via 8 the parameter

EDIT: an example, where parameter 7 @p1 is intended to be a table name

create procedure dbo.uspBeAfraidBeVeryAfraid ( @p1 varchar(64) ) 
AS
    SET NOCOUNT ON
    declare @sql varchar(512)
    set @sql = 'select * from ' + @p1
    exec(@sql)
GO

If @p1 6 is selected from a drop-down list it is 5 a potential sql-injection attack vector;

If 4 @p1 is formulated programmatically w/out 3 the ability of the user to intervene then 2 it is not a potential sql-injection attack 1 vector

A buffer overflow is not SQL injection.

Parametrized 5 queries guarantee you are safe against SQL 4 injection. They don't guarantee there aren't 3 possible exploits in the form of bugs in 2 your SQL server, but nothing will guarantee 1 that.

Your data is not safe if you use dynamic 15 sql in any way shape or form because the 14 permissions must be at the table level. Yes 13 you have limited the type and amount of 12 injection attack from that particular query, but 11 not limited the access a user can get if 10 he or she finds a way into the system and 9 you are completely vunerable to internal 8 users accessing what they shouldn't in order 7 to commit fraud or steal personal information 6 to sell. Dynamic SQL of any type is a dangerous 5 practice. If you use non-dynamic stored 4 procs, you can set permissions at the procesdure 3 level and no user can do anything except 2 what is defined by the procs (except system 1 admins of course).

It is possible for a stored proc to be vulnerable 2 to special types of SQL injection via overflow/truncation, see: Injection 1 Enabled by Data Truncation here:

http://msdn.microsoft.com/en-us/library/ms161953.aspx

Just remember that with parameters you can 7 easily store the string, or say username 6 if you don't have any policies, "); drop 5 table users; --"

This in itself won't cause 4 any harm, but you better know where and 3 how that date is used further on in your 2 application (e.g. stored in a cookie, retrieved 1 later on to do other stuff.

You can run dynamic sql as example

DECLARE @SQL NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000);

SELECT  @ParameterDefinition = '@date varchar(10)'

SET @SQL='Select CAST(@date AS DATETIME) Date'

EXEC sp_executeSQL @SQL,@ParameterDefinition,@date='04/15/2011'

0