CODEWITHSUNDEEP
c#asp.netsql-injection
Kadaw Tomtom
Kadaw Tomtom

Reputation: 73

Parameters and SQL Injection asp.net

I used parameters in the whereclause, but what about the variables for this {0}. Do I need to create a parameter for it to prevent sql injection?

("...inner join db1.dbo.table1.id on db2.dbo.table2.id = {0}.dbo.table3.id where name=@name",abc)

var abc = ddl2.SelectedItem.Text;

cmd.Parameters.AddWithValue("@name", ddl1.selectedvalue);

Upvotes: 0

Views: 224

Answers (4)

Raul G
Raul G

Reputation: 329

Unfortunately, as Abbath already mentioned, this type of construct is not parameterizable. As Abbath mentioned, the best solution is to keep such arguments under your absolute control, but there are times, where such constructs are needed, and it may not be possible to have complete control over them.

For such scenarios, the best recommendation in this case is to escape the arguments. In this case, the DB name represented by {0} on your sample code. There are two potential mechanisms to achieve this:

a) Create a mechanism that allows you to parameterize the query

Advantage: You can reuse the same solution from any driver (.Net, ODBC, etc.)

Disadvantage: A bit more work. You will not use select directly anymore on this case.

For example (I am including a simple example that has an inner join, just like your code):

CREATE PROC sp_MyQuery( @target_db_name sysname, @name nvarchar(100))
AS
BEGIN
    DECLARE @cmd nvarchar(max)
    DECLARE @parameters nvarchar(max)
    SELECT @cmd = N'SELECT * FROM msdb.sys.objects inner join ' 
        + quotename(@target_db_name) + N'.sys.sql_modules 
        on msdb.sys.objects.object_id = ' 
        + quotename(@target_db_name) + N'.sys.sql_modules.object_id WHERE name = @name'
     print @cmd -- See the command before it is executed.
    set @parameters = N'@name nvarchar(100)'
    EXEC sp_executesql @cmd, @parameters, @name = @name
END
go

-- Example of usage
DECLARE @target_db_name sysname = 'msdb'
DECLARE @name nvarchar(100) = 'sp_help_operator'
EXEC sp_MyQuery @target_db_name, @name 
go

At this point, you can use SqlParameter objects as you would normally do. For example:

sqlcmd.CommandText = @"[dbo].[sp_MyQuery]";
sqlcmd.CommandType = System.Data.CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@target_db_name", ddl0.selectedvalue);
sqlcmd.Parameters.AddWithValue("@name", ddl1.selectedvalue);
SqlDataReader reader = sqlcmd.ExecuteReader();

b) Escape the DB name within your CLR code

Advantage: Easier to implement

Disadvantage: App-specific solution, you need to be careful with potential Unicode–DB collation translation issues.

For example (same query as above):

sqlcmd.CommandText = String.Format(@"
    SELECT * FROM msdb.sys.objects inner join [{0}].sys.sql_modules on msdb.sys.objects.object_id = [{0}].sys.sql_modules.object_id WHERE name = @name;",
    ddl0.selectedvalue.Replace("]", "]]"));
sqlcmd.CommandType = System.Data.CommandType.Text;
sqlcmd.Parameters.AddWithValue("@name", ddl1.selectedvalue);
SqlDataReader reader2 = sqlcmd.ExecuteReader();

I typically recommend using solution (a) whenever it is possible, but both solutions should help you protect against SQL injection.

BTW. The following link may also be quite useful: https://blogs.msdn.microsoft.com/raulga/2007/01/04/dynamic-sql-sql-injection/

I hope information helps.

Upvotes: 0

Abbath
Abbath

Reputation: 592

To the best of my knowledge, you can't actually 'paramaterize' database names/table names.

String.Format does not solve SQL injection in this case since it is possible for the user to change ddl2.SelectedItem.Text to whatever they want.

If you need a dynamic value for the database name, I suggest you either keep that value as a const or store it somewhere that you have complete control over/ is never sent or interpreted client side.

Upvotes: 3

Harry
Harry

Reputation: 1

I would suggest you to use any ORM (object relational mapping) i.e Entity framework or n-hibernate etc. and use linq to write queries, that will prevent you application from SQL Injection.

Upvotes: 0

Prasetyo_
Prasetyo_

Reputation: 1

you dont need create parameter for abbc if you use string.format like this

var abc = ddl2.SelectedItem.Text;
string.format("...inner join db1.dbo.table1.id on db2.dbo.table2.id = {0}.dbo.table3.id where name=@name",abc)
cmd.Parameters.AddWithValue("@name", ddl1.selectedvalue);

Upvotes: -2

Related Questions