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.)

Answer 1

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.