CODEWITHSUNDEEP
asp.netsql-serversql-injection
Pablo pas
Pablo pas

Reputation: 25

Prevent SQL injection in queries with exec or execute

I'm developing a reporting web with Visual Studio 2017. All my SQL queries are parameterized and works properly. The problem is in some queries that have exec command like in a procedure.

For example:

Instead of SELECT * FROM tabla WHERE id=@id
I've put exec('SELECT * FROM tabla WHERE id=@id')

The exec case doesn't work. The error message is:

Must declare the scalar variable ""@id""." & vbCrLf & "Must declare the scalar variable ""@id""." Anyone knows a possible solution?

Thanks a lot!

Upvotes: 0

Views: 679

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

Provided you're using parameters in "parameterizable" locations (i.e. you're not trying to parameterize table/column names), you can use sp_executesql to keep using parameters:

exec sp_executesql N'select * from tabla where id=@id',N'@id int',@id = @id

Using exec creates a new context which doesn't have access to any of the outer variables/parameters, but sp_executesql lets you explicitly pass variables into the inner context. (There's no requirement that the inner and outer variable names are the same, but it's often simplest to keep it so)

Upvotes: 4

Related Questions