Dynamic SQL request and SQL injection



  • Let's say there's a procedure for the entry of the list of columns for which the sorting is necessary.

    Can I send them here safely?

      Declare @cmd nvarchar(255)='select * from someTable 
    order by @columnList'
    

    Clearly, I can make Replacement @columnList, but there might be an injection.

    Can you do that? sp_executesql Or do you need to get your hands on the list and put it all in [column]?

    It's not a fight, it's just, it's interesting.



  • (1) Pro sp_executesql

    Parameters that are sent to sp_execsql are placed in a dynamic request as a constant. I mean, SQL is impossible. If on request

    SELECT * FROM T
    ORDER BY @param
    

    We'll set the parameter value for something like "(SELECT 1) DELETE FROM ImportantTable."

    Then we'll eventually get a request:

    SELECT * FROM T
    ORDER BY '(SELECT 1) DELETE FROM ImportantTable'
    

    I mean, a harmless sorting on a constant that the optimist will throw away.

    (2)

    As for what you're allowing to send SQL a code with a list of parameters. There's only a hand to fail that there's no SQL injection.

    In the case of grading, it is safe to use, for example, a set of non-binding parameters that will give the user of your procedure the necessary flexibility to sort the withdrawal.

    If, for some reason, you still accept some SQL code at the entrance-- be ready for his manual handling for undesirable actions for you.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2