-1

This might seem like a silly question, but I'm surprised that I didn't find a clear answer to this already:

Is it possible to use SQL Server parameters for writing a query with dynamic column names (and table names), or does the input just need to be sanitized very carefully?

The situation is that tables and their column names (and amount of columns) are generated dynamically and there is no way to know beforehand to manually write a query. Since the tables & columns aren't known I can't use an ORM, so I'm resorting to manual queries. Usually I'd use parameters to fill in values to prevent SQL injection, however I'm pretty sure that this cannot be done the same way when specifying the table name and/or column names. I want to create generic queries for insert, update, upsert, and select, but I obviously don't want to open myself up to potential injection. Is there a best practices on how to accomplish this safely?

Just as an FYI - I did see this answer, but since there's no way for me to know the column / table names beforehand a case statement probably won't work for this situation.

Environment: SQL Server 2014 via ADO.NET (.NET 4.5 / C#)

Community
  • 1
  • 1
JNYRanger
  • 6,829
  • 12
  • 53
  • 81
  • Well, you don't need to know column/table names if you do a dynamic query. However, if you pass different tables, you need to know the relationships between them. If that's the case, you have to pass the relationships in parameters as well. – Kaf Aug 17 '15 at 16:27

1 Answers1

2

There is no mechanism for passing table or column references to procedures. You just pass them as strings and then use dynamic SQL to build your queries. You do have to take precautions to ensure that your string parameters are valid.

One way to do this would be to validate that all table and column reference strings have valid names in sys.tables and sys.columns before building your T-SQL queries. Then you can be sure that they can be used safely.

You can also use literal parameters with dynamic sql when using the sp_executesql procedure. You can't use it to validate your table and column names, but it validates and prevents SQL injection with your other parameters.

Brian Pressler
  • 6,653
  • 2
  • 19
  • 40
  • I've read a bit up on using sp_executesql, but haven't ever used it. I really like the idea of using the metadata in the database for validation though. – JNYRanger Aug 17 '15 at 17:29