none
Requirements are dynamic table names. Any way to minimize the risk? RRS feed

  • Question

  • Hi,

    So we have a requirement for using dynamic table names, e.g. like this:

    var commandText = string.Format("UPDATE [{0}] SET Name=@Name", tableName);

    Now we can't parameterize the table name, but I'm thinking that we could apply some logic to validate the variable "tableName"? For example that it can't contain whitespace or ";"? Does that make sense or just a Waste of time?

    --
    Werner

    Tuesday, March 12, 2013 12:21 PM

Answers

  • OK, well you posted .NET code so I assume that the app (client) is executing dynamic SQL. This being the case, the app is subject to SQL injection regardless of the validation you have in your .NET code.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, March 13, 2013 12:49 PM

All replies

  • Any validation of dynamic SQL should be on the server side, since checking on the client side does not really reduce the risk of SQL injection. You can still pass the table name and the rest of the SQL as parameters to a stored procedure and perform the validation and execution there. The below link illustrates:

    http://stackoverflow.com/questions/1246760/how-should-i-pass-a-table-name-into-a-stored-proc/1246848#1246848

      

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, March 12, 2013 12:44 PM
  • Any validation of dynamic SQL should be on the server side, since checking on the client side does not really reduce the risk of SQL injection. You can still pass the table name and the rest of the SQL as parameters to a stored procedure and perform the validation and execution there. The below link illustrates:

    http://stackoverflow.com/questions/1246760/how-should-i-pass-a-table-name-into-a-stored-proc/1246848#1246848

      

    Paul ~~~~ Microsoft MVP (Visual Basic)

    But this is not a client; and Exec(sql) is not something we want. The table names would come from database/configuration/whatever of the Windows service. You could argue that if the table name is located serverside there is no real problem. But still it feels wrong to make this hole.


    Tuesday, March 12, 2013 1:24 PM
  • OK, well you posted .NET code so I assume that the app (client) is executing dynamic SQL. This being the case, the app is subject to SQL injection regardless of the validation you have in your .NET code.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, March 13, 2013 12:49 PM