0

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 = @variable";

The SQL Parameterization works with variables, but how do I get it to work with table names? myID is an int I get passed in and changed (can be converted to string), but how do I protect against sql injections here?

Community
  • 1
  • 1
cdub
  • 24,555
  • 57
  • 174
  • 303
  • 2
    [Whitelist](http://en.wikipedia.org/wiki/Whitelist) - this is your next best friend. See the answers to [my related question](http://stackoverflow.com/questions/9651582/sanitize-table-column-name-in-dynamic-sql-in-net-prevent-sql-injection-attack) as there are at least 3 possible approaches: filter (true whitelist), guard (heuristic whitelist), quote/escape. –  Dec 14 '12 at 22:34
  • 1
    Why isn't `myID` just another column in `MyTable`? Is this a multi-tenant application? – Robert Harvey Dec 14 '12 at 22:35
  • Agree with @pst. The list of table names is finite; compare all input against that. – Tim M. Dec 14 '12 at 22:36
  • 2
    Agree with @RobertHarvey. Having dynamic table names is bad design. Add a MyID column to the PK of a single MyTable. – GolezTrol Dec 14 '12 at 22:39

4 Answers4

5

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.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • yes, i realized since myId is in another table, i can test that it exists there and if it does and it's numeric and can proceed. – cdub Dec 14 '12 at 23:01
4

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

DECLARE @TableName VARCHAR(100) = 'Table to Look for';
DECLARE @Exists BIT = ( SELECT CAST( COUNT(1) AS BIT ) FROM sys.tables WHERE name = @TableName AND 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).

Tim M.
  • 53,671
  • 14
  • 120
  • 163
1

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

Igor
  • 15,833
  • 1
  • 27
  • 32
-1

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.

Sten Petrov
  • 10,943
  • 1
  • 41
  • 61
  • Redesigning *can* be an answer, but some situations (e.g. dealing with dynamic input data and automated processes) require using dynamic tables. – user247702 Aug 16 '17 at 14:11
  • @Stijn There's absolutely no reason to have dynamic names (until you've proven it with an example I can't refactor). Dynamic names most likely mean just one more reference table. I'm *really* curious to see such an example that can't be solved without dynamic stuff – Sten Petrov Aug 17 '17 at 01:27
  • @Stijn in fact the accepted answer proves that: it's the sys.tables table that's used as a reference table – Sten Petrov Aug 17 '17 at 01:32
  • In theory, I could refactor to use a reference table. However, I don't control the full software stack and I have to adhere to standards and conventions. My specific example: I'm storing and processing geometric data, and commonly the table name describes what kind of geometries are stored inside, e.g. 'houses' or 'roads'. I *could* use GUIDs as table names and create a reference table, but I'd be deviating from conventions. My point being: you don't always have full control but at the same time you still want to be as secure as possible. – user247702 Aug 17 '17 at 08:26
  • Also, I can't use the sys.tables approach when creating the table is part of the automated process. – user247702 Aug 17 '17 at 08:28
  • @Stijn Your example is an obvious counterexample where you'd store the object type in a field of a table called Geometries... Having tables such as house, aptBuilding, gasStation, parking lot, officeBuilding, hospital.... is just horrible design. It makes it near impossible to maintain the system and add features. Whatever the "conventions" are it would be cheaper to sacrifice them than all maintainability – Sten Petrov Aug 17 '17 at 18:12