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);
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.
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.
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.
[ and ], for example: ‘CREATE TABLE [ta'b]]le](data int). As you can see, the table ta’b]le` contains characters that need to be escaped. When constructing SQL statements that cannot be parameterized, you need to escape the ] character that would otherwise indicate the end of the object name. This is similar to how quotename works: DECLARE @name sysname = N'ta''b]le';DECLARE @cmd nvarchar(max) = N'SELECT * FROM ' + quotename(@name);print @cmd; EXEC(@cmd); -- "SELECT * FROM [ta'b]]le]"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);
