Do you need parameterized SQL searches if you check the inputs?

Question

I'm writing an R Shiny/SQLite app. In the app, I have a function that returns a column from one of the tables in my SQLite database, with the function taking the table name as an input.

Before sending the query to SQLite, the function checks that the table name equals one of the table names that the user is allowed to access. However, I am not using a parameterized query, because the term I'm changing is not a variable used for comparison but the name of the table to extract information from. (There might be a way to make this work anyway with a parameterized search, I suppose.)

My question is whether this is safe from an SQL injection? Can the query be altered on its way from the server to the database, or only from an alteration in the ui input to the server?

(Bear with me, I am new to SQLite.)

The question is, why you're trying to bargain? Are prepared statements that scary? Why you're trying to avoid a standard practice? — Your Common Sense, Aug 03 '20 at 17:12

score 2 · Answer 1 · answered Jul 29 '20 at 17:07

Assuming your query is being concatenated as follows:

tbl <- "yourTable"
sql <- paste0("select * from ", tbl, " where some_col = 1")

Then there should be no chance of SQL injection, assuming you check the incoming table name and verify that it matches a table name in your whitelist. Note that this step is critical here to keeping things safe. Let's say that you didn't sterilize the incoming table name. Then, consider this:

tbl <- "yourTable; delete from yourTable"

This would result in the following query being submitted for execution:

select * from yourTable; delete from yourTable where some_col = 1;

Assuming your SQLite driver allows multiple SQL statements to execute, the above hack/trick might end up deleting data from a large portion of one of your tables.

So, your approach should be safe provided that you check the table name. Note that strictly speaking the table name itself is not a parameter in a parameterized query. Rather, only the literal values in the query are parameters.

Olivier Jacot-Descombes · Answer 2 · 2020-08-03T16:25:55.000

The problem of SQL injection is only the user input. Nothing happens to the query on its way from the server to the database (well a malware could in theory alter it, but then even a parametrized query wouldn't help).

I.e., if you create a SQL string like this (C#):

sql = "SELECT * FROM " + tableName;

Then a user might enter a tableName like

MyTable; DROP TABLE MyTable

Guess what happens.

So, if you check the table name, you are on the safe side.

Bill Karwin · Accepted Answer · 2020-08-03T16:20:43.847

SQL query parameters cannot be used in place of a table name anyway, so comparing the table name to a list of known authorized tables is your only option.

Yes, it is safe. If you're in control of the set of values that can be interpolated into the SQL query, then you can prevent unauthorized SQL injection.

Note that some other elements of SQL queries cannot be parameters:

Any identifier, e.g. a table name, column name, or schema name.
Expressions
Lists of values in an IN ( ... ) predicate. Use one parameter per value in the list.
SQL keywords.

A query parameter can be used only in place of a single scalar value. That is, where you would use a quoted string literal, quoted date literal, or numeric literal.

Do you need parameterized SQL searches if you check the inputs?

3 Answers3