Qfyilyi

I was given a task to rewrite an old web API.

This API reads SQL queries from the database.

There's literally a view with "Queries" in the name which contains "SqlText" column.

SELECT SqlText FROM Queries WHERE QueryID = 123

The "SqlText" contains only simple SQL queries in the format SELECT [columns] FROM [table] by convention.

The query is altered depending on the URL parameters in the request. The result of this query is then shown as result.

string parsedColumns = ParseColumns(queryRow); //contains "Column1, Column2";

string parsedTable = ParseTable(queryRow); //contains "SomeTable"



string requestColumns = HttpContext.Request["columns"];

string sqlColumns = requestColumns ?? parsedColumns;



string col1Condition = HttpContext.Request["Column1"]

string col2Condition = HttpContext.Request["Column2"]



string sqlQuery = "SELECT " + sqlColumns 

                  + " FROM " + parsedTable 

                  + " WHERE Column1 = " + col1Condition 

                  + " AND Column2 = " + col2Condition;

This is obvious SQL injection issue so I started rewritting it.

Now there are three other problems.

• I cannot change the structure of the database or the convention


• The database is either Oracle or SQL Server


• I don't know how to correctly work with the "columns" URL parameter to avoid SQL injection.

It's easy to convert the URL parameters in the WHERE clause to the SQL parameters for both SQL Server and Oracle.

SQL Server

var sqlCommand = new SqlCommand("SELECT * FROM SomeTable WHERE Condition1 = @con1 AND Condition2 = @con2");

Oracle

var oracleCommand = new OracleCommand("SELECT * FROM SomeTable WHERE Condition1 = :con1 AND Condition2 = :con2");

Column identifiers

The problem is with the HttpContext.Request["columns"]. I still need to somehow alter the SQL query string with URL parameters which I don't like at all.

To simplify the issue, let's consider a single column from URL request.

string column = HttpContext.Request["column"];

var cmd = new SqlCommand($"SELECT {column} FROM ...");

I know that in SQL Server the identifier can be surrounded by braces. So my line of thinking is that I'm safe if I strip all braces from the column.

string column = HttpContext.Request["column"];

column = column.Replace("[", "").Replace("]", "");

column = $"[{column}]";

var cmd = new SqlCommand($"SELECT {column} FROM ...");

Oracle uses quotation marks.

string column = HttpContext.Request["column"];

column = column.Replace(""", "");

column = $""{column}"";

var cmd = new OracleCommand($"SELECT {column} FROM ...");

The question

• Is this sql-injection safe enough?


• Or is this use case inherently sql-injection unsafe?

Since you are working with a basic program design that you cannot change what about just trying to add edits to the input to look for injection elements. For example if the input is a column name it will need to have a maximum length of 30 (before 12.x) characters and should not contain a semicolon or the strings " OR" or " AND" in them. While not a perfect solution this should be practical solution.