Avoid SQL Injections on query with tablename [duplicate]

L

4

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?

Luci answered 14/12, 2012 at 22:33 Comment(4)

Whitelist - this is your next best friend. See the answers to my related question as there are at least 3 possible approaches: filter (true whitelist), guard (heuristic whitelist), quote/escape. – Heterophyte 14/12, 2012 at 22:34

Why isn't myID just another column in MyTable? Is this a multi-tenant application? – Otoplasty 14/12, 2012 at 22:35

Agree with @pst. The list of table names is finite; compare all input against that. – Mellar 14/12, 2012 at 22:36

Agree with @RobertHarvey. Having dynamic table names is bad design. Add a MyID column to the PK of a single MyTable. – Selfrealization 14/12, 2012 at 22:39

M

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).

Mellar answered 14/12, 2012 at 22:41 Comment(2)

do i grab @exists after wards to test and if it exists, then run my query? – Luci 14/12, 2012 at 22:45

Yes, e.g. IF( @Exists = 1) BEGIN – Mellar 14/12, 2012 at 22:45

G

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.

Gerous answered 14/12, 2012 at 22:44 Comment(1)

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. – Luci 14/12, 2012 at 23:1

M

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).

Mellar answered 14/12, 2012 at 22:41 Comment(2)

do i grab @exists after wards to test and if it exists, then run my query? – Luci 14/12, 2012 at 22:45

Yes, e.g. IF( @Exists = 1) BEGIN – Mellar 14/12, 2012 at 22:45

F

1

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

Floria answered 14/12, 2012 at 22:40 Comment(0)

P

-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.

Plerre answered 14/12, 2012 at 22:45 Comment(6)

Redesigning can be an answer, but some situations (e.g. dealing with dynamic input data and automated processes) require using dynamic tables. – Lanate 16/8, 2017 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 – Plerre 17/8, 2017 at 1:27

@Stijn in fact the accepted answer proves that: it's the sys.tables table that's used as a reference table – Plerre 17/8, 2017 at 1: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. – Lanate 17/8, 2017 at 8:26

Also, I can't use the sys.tables approach when creating the table is part of the automated process. – Lanate 17/8, 2017 at 8: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 – Plerre 17/8, 2017 at 18:12

Recommended topics

Hot tags