CODEWITHSUNDEEP
c#sqlsql-injection
Rock Anthony Johnson
Rock Anthony Johnson

Reputation: 169

How vulnerable is the this dynamically created sql?

I can find so much information on the web about the proper and safe way of dynamically creating parameterized queries. However such websites only talk about parameterization of the where clause.

Well, what about the other clauses of a sql statement? See the following:

       string sql = string.Format(@"                
            SELECT MIN(TableName) as TableName, {0}
            FROM
            (
              SELECT 'Table A' as TableName, {0}
              FROM {1}
              UNION ALL
              SELECT 'Table B' as TableName, {0}
              FROM {2}
            ) tmp
            GROUP BY {0}
            HAVING COUNT(*) = 1", columnList, tableA, tableB);

I'm constructing a statement whereby the following has been parameterized:

  1. The items in the select clause
  2. The tablename in the from clause

Question: How vulnerable is this to sql injection that can cause some damage?

I can't think of anyway a malicious hacker could inject sql that will result in properly formed, executable sql. But then again, I'm not an sql expert.

Upvotes: 0

Views: 130

Answers (2)

Svek
Svek

Reputation: 12858

As @Andrew states in his answer, if the parameter(s) are not impacted by any user input, or otherwise gets validated/cleaned it should be fine.

To answer your point about a possible properly formed SQL statement... The only areas that draw concern are potential escaped characters (such as a semi-colon, that can technically be placed anywhere in the command block) and/or something well-formed at the {1} and {2} sections of the statement.

Upvotes: 2

Andrew
Andrew

Reputation: 1596

It really depends on where columnList, tableA, and tableB come from. If they come from your code, with no user input, then it's pretty safe. If the user specifies the table names, you have to be ready to meet Little Bobby Tables.

In my company's main c# application we use something similar for declaring columns, but the columns in the SQL tables are defined in a class for that table, so we can build select, add, update and create table strings from that class. Never does the user get to define those columns.

Upvotes: 5

Related Questions