0

I have a stored procedure which creates dynamic SQL from user input and executes it.

create proc MySP @input nvarchar(max)
as
declare @sql nvarchar(max) = // generate sql using @input and data from tables
-- The generated SQL will need to access linked server too
exec(@sql)

To prevent SQL injection attack of insert, delete, update, drop, etc. (select is ok), will exec(@sql) as login='aReadonlyLogin' work? Is it a way without using a read-only login? The developer may not have permission to create a new login.

ca9163d9
  • 24,345
  • 44
  • 175
  • 352
  • 1
    this entire thing IS sql injection.. how do you expect to prevent it? – Joe Phillips Nov 02 '18 at 21:25
  • 1
    it's still SQL injection whether it's reading or not. It sounds like you want to prevent anything besides SELECT? – Joe Phillips Nov 02 '18 at 21:27
  • What ever problem you are trying to solve, you are going about it the wrong way. Just my humble opinion – S3S Nov 02 '18 at 21:28
  • Read (select) is ok, just need to prevent insert, update, drop, and delete, etc. – ca9163d9 Nov 02 '18 at 21:29
  • Guarding against sql injection likely has more to do with how you handle the `@input` variable than how you handle the linked server login. – Eric Brandt Nov 02 '18 at 21:29
  • Can you show an actual example of a "good" and a "bad" input value? – Joe Phillips Nov 02 '18 at 21:29
  • 3
    User input is a weapon. The easiest way to avoid SQL injection is to not accept user input and execute it blindly. – Aaron Bertrand Nov 02 '18 at 21:30
  • 2
    "User input is a weapon." I'm going to credit you every time, but I'm going to steal that, @AaronBertrand – Eric Brandt Nov 02 '18 at 21:31
  • 2
    If you're going to let a user type a whole query, pass it in, and execute it, you're just in for a world of hurt. You can do all the sanitizing and replacing and escaping you want, a determined person **will** get past it, I promise you. You can see some other questions and tips [here](https://stackoverflow.com/questions/2200256/how-can-i-avoid-sql-injection-attacks), [here](https://www.mssqltips.com/sqlservertip/3637/protecting-yourself-from-sql-injection-in-sql-server--part-1/), [here](https://www.mssqltips.com/sqlservertip/3638/protecting-yourself-from-sql-injection-in-sql-server--part-2/). – Aaron Bertrand Nov 02 '18 at 21:44
  • @EricBrandt I can't claim that as original copyright, but I do forget where I picked up the concept. – Aaron Bertrand Nov 02 '18 at 21:45
  • Let users input *values*, but don't let a user input *code* (an SQL query counts as code). – Bill Karwin Nov 03 '18 at 17:17
  • A `select` can do damage too. It may not modify data, but it can generate load on your database server that will crash the server. – Bill Karwin Nov 03 '18 at 17:19

0 Answers0