SQL Injection in FROM clause with SqlBuilder

Question

We have an SQL statement that uses SqlBuilder, sets the table name in the from clause. Database is SQL Server 2008 and higher.

var sqlBuilder = new SqlBuilder();

sqlBuilder.Select("*").From(tableName);
sqlBuilder.Where("...");

Connection.BuilderQuery<dynamic>(sqlBulder).Select(Map);

I am wondering if this is a risk of SQL injection? and how can I mitigate this risk? Or SqlBuildercares about these things?

Can I reduce the risk simply by wrapping the table name in square brackets? eg.

sqlBuilder.From("[" + tableName + "]");

It would also be very helpful if someone could provide some examples of an SQL injection attack in a sentence FROMso that I can understand how this works and create tests.

Answer 1

, SqlBuilder, : , , :

var myFullQuery = string.Format("SELECT * FROM {0} WHERE A = 1", externalInput);

. : ValidTableName; DELETE FROM ValidTableName; SELECT * FROM ValidTableName

myFullQuery : SELECT * FROM ValidTableName; DELETE FROM ValidTableName; SELECT * FROM ValidTableName WHERE A = 1, ... , ...

Answer 2

, , . , SqlBuilder , , SqlBuilder, .NET. tableName, SqlBuilder .

, . :

sqlBuilder.From("[" + tableName + "]");

:

information_schema.columns]; DROP TABLE x; SELECT * FROM [y

.

, , :

https://github.com/maxtoroq/DbExtensions/blob/master/docs/SqlBuilder.md

, , , , , MySql ( LIMIT, MySql SQL).

Answer 3

SqlBuilder ?

. , . , , , .

, SQL?

. - , , , . , , . int;

public enum WhitelistTable
{
  Undefined = 0,
  MyTable1,
  MyTable2
}

int, . DescriptionAttribute .ToString() . , Blacklists.

var myTable = WhitelistTable.MyTable1;

sqlBuilder.From(myTable.ToString());

, ?

, ( 100% SqlBuilder). , . :

SomeTable]; Drop Table SomeTable; -

:

sqlBuilder.From("[SomeTable]; Drop Table SomeTable; --]");

Answer 4

sqlBuilder.Select("*").From(tableName);

tableName , = Sql Injection

sqlBuilder.Where("...");

. Where . -, where. SqlBuilder ? - , .

: SqlBuilder ?

: . , , SqlBuilder . , , , , . sql-, where.

Answer 5

- , SqlBuilder, , ExecuteScalar()

select count(*) from information_Schema.tables where table_name = @tbl

If the result was> 0, then I would use the following (if the result is 0, then you should come off)

string sql = "Select a, b, c from ["+tblName+"]"

This is dirty, but using an information scheme should ensure that the table name exists in the database and that the variable contains nothing to the contrary.