SQL Injection
I want to inject a "where" criteria parametrically, but I can't get this to work:
CREATE PROCEDURE dbo.CopyTestCases
@Criteria varchar(255)
AS
declare @t table(NID int not null);
set transaction isolation level serializable;
begin tran;
insert into TestIT (Product,CatID,Category,Title)
output inserted.TestID into @t( NID)
select Product,CatID,Category,Title
from TestIT where@Criteriaorder by TestID;
commit;
GO
I get the message "An expression of non-boolean type specified in a context where a condition is expected". How do I fix this?
If you search the forums for dynamic sql queries you will come up with alot of suggestions.
You can look at exec and one other function that is not rolling off the tip of my tongue.
I tried "exec," wrapping most of the expression in a string and using concatenation for the criteria variable, and it doesn't work. It just doesn't do anything. Nada. As if I didn't execute the stored proc.
Like I stated earlier if you searched for exec sql in the forums you would come up with alot. The first result is where i grabbed ths syntax for this which just needs to be modified
SET @SQL = @SQL + ' SELECT @Result = (SUM((' + @ColumnA + ' )) FROM ' + @table
exec sp_execsql @sql, N' @Result float Output', @Result output
would become
SET @SQL = 'select columns from tablename where ' + @WhereClause + ' ORDER BY column'
exec sp_execsql @SQL
Since you're not actually passing any parameters you dont have to pass them as parameter values
I found the problem with "Exec()" was that it operates in its own exclusive scope. That is, it does not recognize any variables declared outside its context, that are referenced inside its text. This is very unfortunate, because it has now forced me to add the declration(s) to the string to be evaluated, as well as other sql expressions that make reference to the variables. It has become a very long string - though yes it does now work.
However, I am confused by your suggestion of "sq_execsql," which did not work at all. When I tried it, I was told: "Cannot add rows to sysdepends for the current object because it depends on the missing object 'sp_execsql'. The object will still be created."
I'm surprised there is nothing on google or msnsearch on that topic. And when I say "nothing," I mean that zero results are returned on both engines when I enter "depends missing object sp_execsql".
I am not :) It is sp_executesql. It is a mistake that I make occasionally when trying to use the statement.
And no, all variables are scoped to the batch/object where they were created. Dynamic SQL is a real pain to work with for just this reason. One other note, be sure that your strings will hold up to ANY value inserted. EVEN DROP DATABASE! Basically this is usually done with quotename to make sure that all strings have proper quoting (double quotes in the middle of strings) and so that something along the lines of O'Malley doesn't cause an error.
This is the gold standard of articles about the subject: http://www.sommarskog.se/dynamic_sql.html
