CODEWITHSUNDEEP
c#sqldata-access-layer
DaGrooveNL
DaGrooveNL

Reputation: 187

Mitigate SQL injection and keep current flexibility

I need to update a C# .NET Framework library which handles most of our SQL tasks. Currently this library is vulnerable for SQL injection.

I found out that using parametrized queries safeguards against SQL injection. However, for a lot of our programs we are using variable tableNames, variable columnNames and sometimes even a dynamic range of columns (i.e. after deployment, extra columns can be added to a table and the software can perform CRUD-actions on those).

From what I understand, when using parametrized queries, it is not possible to use a parameter for a columnName or tableName, unless you use EXEC(N''), but if I understand correctly this would make us vulnerable again to SQL injection?

Lastly, because of the need to support adding columns in a table post-deployment, I don't think it is possible to use a DAL like Entity Framework (without having to recompile the code after adding a column post-deployment).

I am a bit at a loss how I can resolve the vulnerability regarding SQL injection whilst still providing all the current 'dynamic' functionality.

Does anyone have an idea?

Upvotes: 0

Views: 68

Answers (1)

AKX
AKX

Reputation: 169338

In general, by stringently validating the user-entered data that can't be parametrized otherwise.

  • To be as stringent as possible, use an allowlist of allowed terms. Since you say you need to be able to add columns post-deployment, you could interrogate the particular database's schema to build that list of allowed words.
  • To be more flexible without the need to figure out an allowlist, come up with other validation, e.g. a regular expression that matches the terms that you should allow, but doesn't allow for SQL injection. For instance, if all of those column names or table names are always lower-case with underscores, if all the user-entered terms match the regular expression ^[a-z_]+$, there's little chance for any shenanigans.

Upvotes: 3

Related Questions