Avoiding SQL injection while constructing SELECT

Question

I want to avoid SQL Injections. I am posting the question by simplifying the problem I am working at.

The client wants to view a set of columns from a table. It passes the table name and a list of columns. The client is aware of the table name and the all possible list of columns through a secured API.

On the server, I am constructing a SELECT query using the table name and list of columns passed.

I cannot use a view.

To avoid SQL injection, this is what I am planning to do.

Check if the columns passed are part of the all possible list of columns.
Check if column contains any characters like =, -, + to avoid any security issues.

Am I missing anything here?

You should probably check the OWASP SQL injection cheat sheet to make sure: https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet — Jeroen Steenbeeke, Jul 30 '18 at 07:15
Thank you @JeroenSteenbeeke. If I understand correctly my #1 is 'Option 3: White List Input Validation' in the cheat sheet. Thanks again for your input. — DKG, Jul 30 '18 at 07:28

score 4 · Answer 1 · answered Jul 30 '18 at 07:29

4

Query the catalog to check that the entered table name really exists in the database. (And likewise for checking that the entered column names really are columns of the named table.)

answered Jul 30 '18 at 07:29

Erwin Smout

18,113
4
33
52

And make that query a parameterized one, otherwise it will be just another query vulnerable to SQLi. :) – Gabor Lengyel Jul 30 '18 at 19:03
1

Yes, obviously :-) – Erwin Smout Jul 31 '18 at 07:56

Avoiding SQL injection while constructing SELECT

1 Answers1