CODEWITHSUNDEEP
sql.netsql-serversql-injection
Rekesoft
Rekesoft

Reputation: 150

Best way of sanitize unparametrizable sql

I have to make a SQL string to be injected in a database for a third party to read it, execute it, and making a report with the results. Since the user can choose the columns wanted for the report, as well as renaming the columns, I've ended with a code like this:

string sql = "SELECT ";
foreach(KeyValuePair<string, string> field in report.fields)
{
    sql += "[" + field.Key + "] as [" + field.Value + "];
}
sql += " WHERE idrpt=@id";

The only part of this query I can parametrize is the WHERE clause, but if my research on the web has'nt been misguided, there's no way to parametrize the column names and aliases in the SELECT clause. Now, given that I can't change the way the program works (I have to produce a valid SQL query to a third party to execute it), what would be the best way of sanitize the input string?

I've solved the part about column names by checking them against a list of valid columns, but I can't do that for the aliases, which can be whatever string of less than 80 characters the user is willing to give.

Upvotes: 1

Views: 2036

Answers (2)

Magisch
Magisch

Reputation: 7352

Right, so you have a SQL layout you can't change that necessitates you to do this. That is unfortunate, but lets make the best of it.

As you stated in your comments, you probably need some special character support, so specifically escape these special characters.

Other then that, you should reduce the allowed names to alphanumeric characters and possibly whitespace. Validate these against your validation mechanism of choice, for instance regex, and allow only those characters. That will probably keep you mostly safe from SQL injection.

This isn't optimal, but it seems to be the best you can do in this situation.

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

As you noted there is no way to parametrize the column names and aliases. Therefore, you are opened to SQL injection. To minimize the issue you can use quotename, which is similar to the approach you are using currently.

string sql = "SELECT ";
foreach(KeyValuePair<string, string> field in report.fields)
{
  sql += "quotename(" + field.Key + ") as quotename(" + field.Value + ")";
}
sql += " WHERE idrpt=@id";

Upvotes: 1

Related Questions