1

We have an ASP.NET Web API controller that accepts a comma-delimited string of database column names from the query string. This string of column names is checked to ensure only alpha characters, underscores, and commas are present in the string; no spaces, special characters or numbers allowed. This string is eventually added to a SQL statement via string interpolation exactly as passed to the API in the query string.

Any other query string parameters are added to the SQL statement as parameters. We encountered an issue with parameterizing a list a columns not be interpreted correctly by Oracle so we ended up with this solution.

Although not ideal, are there any additional steps to prevent SQL injection attacks via this vector?

We are currently using Dapper for data access but frequently use plain ADO.NET.

DenaliHardtail
  • 27,362
  • 56
  • 154
  • 233
  • Get in use a OData and Entity Framework Library. If you go with OData for expanding a columns and Entity Framework as access into database, there will be impossible to make SQL Injection. – CrazyBaran Apr 25 '18 at 17:24

1 Answers1

2

If that's the only thing you can do, as long as you make sure you don't allow numbers and special characters like quotes, equal and different you will be fine. I would even say you should be good with numbers, I have seen cases where numbers are actually used as part of column names so you could relax that restriction a little.

You could basically create a white list of characters allowed and when the request comes in, if any character is not part of that list then you can throw Bad Request and stop right there. A white list is much better than a black list as it can be much shorter and it's easier to maintain.

Another layer of protection can come from the API itself. Make sure you :

  1. authenticate the access to your controller, no anonymous users allowed, maybe something like OAUTH2 if you can.
  2. turn that API call into a POST and send the list of columns in the body of the request not in the query string.
  3. issue the request over HTTPs.

All these things together should keep you well protected as no one can now watch and see what you are sending through and they can't issue their requests either if you protect your endpoint properly.

Andrei Dragotoniu
  • 6,155
  • 3
  • 18
  • 32