Sanitize table/column name in Dynamic SQL in .NET? (Prevent SQL injection attacks)

by

I’m not sure if you’re still looking into this, but the DbCommandBuilder class provides a method QuoteIdentifier for this purpose. The main benefits of this are that it’s database-independent and doesn’t involve any RegEx mess.

As of .NET 4.5, you have everything you need to sanitize table and column names just using your DbConnection object:

DbConnection connection = GetMyConnection(); // Could be SqlConnection
DbProviderFactory factory = DbProviderFactories.GetFactory(connection);

// Sanitize the table name
DbCommandBuilder commandBuilder = factory.CreateCommandBuilder();

string tableName = "This Table Name Is Long And Bad";
string sanitizedTableName = commandBuilder.QuoteIdentifier(tableName);

IDbCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM " + sanitizedTableName;

// Becomes 'SELECT * FROM [This Table Name Is Long And Bad]' in MS-SQL,
// 'SELECT * FROM "This Table Name Is Long And Bad"' in Oracle, etc.

(Pre-4.5, you’ll need some other way to get your DbProviderFactory — maybe from the data provider name in your application configuration or hard-coded somewhere.)

More Related Contents:

  1. How do I convert a string into safe SQL String?
  2. store image in database or in a system file? [closed]
  3. SQL Identity (autonumber) is Incremented Even with a Transaction Rollback
  4. SELECT * FROM X WHERE id IN (…) with Dapper ORM
  5. Bulk Insert to Oracle using .NET
  6. Parameterized Queries with LIKE and IN conditions
  7. What is the difference between SqlCommand.CommandTimeout and SqlConnection.ConnectionTimeout?
  8. Does SqlCommand.Dispose close the connection?
  9. Using “GO” within a transaction
  10. Does End Using close an open SQL Connection
  11. Does this code prevent SQL injection?
  12. How do I write LINQ’s .Skip(1000).Take(100) in pure SQL?
  13. Why doesn’t Dapper dot net open and close the connection itself?
  14. How can you name the Dataset’s Tables you return in a stored proc?
  15. How can I create database tables from XSD files?
  16. How can I avoid SQL injection attacks in my ASP.NET application?
  17. Why is some sql query much slower when used with SqlCommand?
  18. Is it possible to send a collection of ID’s as a ADO.NET SQL parameter?
  19. How can I configure Entity Framework to automatically trim values retrieved for specific columns mapped to char(N) fields?
  20. Regular expression to find all table names in a query
  21. How to return a page of results from SQL?
  22. Using a variable for table name in ‘From’ clause in SQL Server 2008
  23. What is the difference between IQueryable and IEnumerable?
  24. Could you explain STA and MTA?
  25. Form_Load() ‘event’ or Override OnLoad()
  26. Generate Delete Statement From Foreign Key Relationships in SQL 2008?
  27. How to use System.Windows.Forms in .NET Core class library
  28. Does LINQ work with IEnumerable?
  29. How to improve painting performance of DataGridView?
  30. System.IO.FileSystemWatcher to monitor a network-server folder – Performance considerations

Leave a Comment