Build SQL query string using user input

Question

I need to make a string using the values ​​that the user selects on a web page,

Suppose I need to display files for several machines with different search criteria ...

I am currently using this code:

DataTable dt = new DataTable(); SqlConnection connection = new SqlConnection(); connection.ConnectionString = ConfigurationManager .ConnectionStrings["DBConnectionString"].ConnectionString; connection.Open(); SqlCommand sqlCmd = new SqlCommand ("SELECT FileID FROM Files WHERE MachineID=@machineID and date= @date", connection); SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd); sqlCmd.Parameters.AddWithValue("@machineID", machineID); sqlCmd.Parameters.AddWithValue("@date", date); sqlDa.Fill(dt); 

Now this is a fixed request in which the user has only one computer and just selects one date ...

I want to make a request in which the user has several search options, such as type or size, if he wants, depending on what he / she chooses.

Also, if he / she can choose several cars ...

 SELECT FileID FROM Files WHERE ( MachineID=@machineID1 or MachineID = @machineID2...) AND (date= @date and size=@size and type=@type... ) 

All this happens at runtime ... otherwise I need to create a for loop to put several machines one after another ... and have multiple requests depending on the case that the user has chosen ...

This is pretty interesting and I could use some help ...

Answer 1

If you are going to do this through dynamic SQL, you need to build an IN function call. (e.g. In (id1, id2, id3 ...)

 private string GetSql( IList<int> machineIds ) { var sql = new StringBuilder( "SELECT FileID FROM Files Where MachineID In(" ); for( var i = 0; i < machineIds.Count; i++ ) { if ( i > 0 ) sql.Append(", ") sql.Append("@MachineId{0}", i); } sql.Append(" ) "); //additional parameters to query sql.AppendLine(" And Col1 = @Col1" ); sql.AppendLine(" And Col2 = @Col2 "); ... return sql.ToString(); } private DataTable GetData( IList<int> machineIds, string col1, int col2... ) { var dt = new DataTable(); var sql = GetSql( machineIds ); using ( var conn = new SqlConnection() ) { conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString; using ( var cmd = new SqlCommand( sql, conn ) ) { conn.Open(); for( var i = 0; i < machineIds.Count; i++ ) { var parameterName = string.Format("@MachineId{0}", i ); cmd.Parameters.AddWithValue( parameterName, machineIds[i] ); } cmd.Parameters.AddWithValue( "@Col1", col1 ); cmd.Parameters.AddWithValue( "@Col2", col2 ); ... using ( var da = new SqlDataAdapter( cmd ) ) { da.Fill( dt ); } } } return dt; } 

Answer 2

You can use WHERE MachineID IN ('Machine1', 'Machine2', 'Machine3', ... 'MachineN')

Then in your loop you simply add 1..n machines. The IN clause works with 1 element or n elements, so it should be fine.

However, I would look at using a stored procedure, not hardcoding SQL in your application.

Answer 3

Create a real table and load the machine IDs into it.

Then your SQL will be:

 where MachineID in ( select MachineID from userMachine where userID = x) 

When you are done, delete all lines for the user ID:

 delete from userMachine where userID = x. 

Answer 4

Ideally, you are trying to find a solution like creating "MachineID in (1, 2, 3, 4)" dynamically.

Option 1

There are many ways to complete this task: pass in a comma-separated string to the stored process and dynamically build the sql string, and then call "EXEC sp_executesql @sql", WHERE IN (array of identifiers)

Option 2

You can pass a string of values ​​separated by commas and then parse the values ​​into their own temporary table and then join it http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Option 3 - My Choice

Now you can pass an array of values ​​using XML and then easily select the elements of the array. http://support.microsoft.com/kb/555266

.

Answer 5

I would also recommend using a stored procedure, because otherwise you will leave yourself open to attack SQL injection - especially where you create a string based on user input.

Sort of:

 a' or 1=1; -- Do bad things 

You can use sp_executesql in SQL to run the SQL statement, which is created using the where clause such as @dcp, and although it will not optimize well, it is probably a quick command to run anyway.

Example SQL Injection Attacks

One way to achieve this is to use charindex. This example shows how to run a stored procedure when passing a list of identifiers separated by spaces:

 declare @machine table (machineId int, machineName varchar(20)) declare @files table (fileId int, machineId int) insert into @machine (machineId, machineName) values (1, 'machine') insert into @machine (machineId, machineName) values (2, 'machine 2.0') insert into @machine (machineId, machineName) values (3, 'third machine') insert into @machine (machineId, machineName) values (4, 'machine goes forth') insert into @machine (machineId, machineName) values (5, 'machine V') insert into @files (fileId, machineId) values (1, 3) insert into @files (fileId, machineId) values (2, 3) insert into @files (fileId, machineId) values (3, 2) insert into @files (fileId, machineId) values (4, 1) insert into @files (fileId, machineId) values (5, 3) insert into @files (fileId, machineId) values (6, 5) declare @machineText1 varchar(100) declare @machineText2 varchar(100) declare @machineText3 varchar(100) set @machineText1 = '1 3 4' set @machineText2 = '1' set @machineText3 = '5 6' select * from @files where charindex(rtrim(machineId), @machineText1, 1) > 0 -- returns files 1, 2, 4 and 5 select * from @files where charindex(rtrim(machineId), @machineText2, 1) > 0 -- returns file 4 select * from @files where charindex(rtrim(machineId), @machineText3, 1) > 0 --returns file 6 

Thus, you can create this stored procedure to achieve your goal:

 create procedure FilesForMachines (@machineIds varchar(1000)) as select * from [Files] where charindex(rtrim(machineId), @machineIds, 1) > 0 

Charindex tip from BugSplat .

Answer 6

Usually, when I want to create a search type query, I use optional parameters. This allows me to send something or nothing to a parameter, making the request from indefinite to very specific.

Example:

 SELECT COL1, COL2, COL3 FROM TABLE WHERE (@COL1 IS NULL OR @COL1 = '' OR @COL1 = COL1) 

As you noted above, if you go to NULL or BLANK, it will not add the parameter to the request. If you specify a value, it will be used in the comparison.