Avoid Sql Injections On Query With Tablename

Possible Duplicate: Sanitize table/column name in Dynamic SQL in .NET? (Prevent SQL injection attacks) I have a query like so: 'SELECT * FROM MyTable_' + myID + ' WHERE variable

Solution 1:

As long as myID is a numeric variable, it can not contain any harmful code.

The only other thing that you would need to do, is to make sure that an error message from trying to read a table that doesn't exist, doesn't leak information about the database layout which could possibly aid in some other kind of attack.

Solution 2:

I question why you are doing this, but you can look at sys.tables for a conclusive whitelist.

DECLARE@TableNameVARCHAR(100) ='Table to Look for';
DECLARE@Exists BIT = ( SELECTCAST( COUNT(1) AS BIT ) FROM sys.tables WHERE name =@TableNameAND type ='U' );

You could parameterize the initial input, but the whitelist approach is still important. Otherwise, a malicious user could pass any valid table name in the entire database and the query would run against it (assuming they had SELECT permissions).

Solution 3:

Get a list of tables in your database and check that "MyTable_" + myID is in that list.

Solution 4:

REDESIGN is the answer, don't have dynamic table names. Have a value inside the table that indicates your original table name and have just one table for all of your current tables.

If you're stuck with something existing that has to be backward compatible with other parts of the system you can (and should) combine approaches. Escaping, whitelisting or referencing are all workable, I'd say pick two.

When I say 'referencing' - put all valid names in a list, pass an integer index to pick one.