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: owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet — Jeroen Steenbeeke
– Jeroen Steenbeeke, Commented Jul 30, 2018 at 7: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
– DKG, Commented Jul 30, 2018 at 7:28

Erwin Smout · Accepted Answer · 2018-07-30 07:29:54Z

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, 2018 at 7:29

Erwin Smout

18.5k4 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gabor Lengyel Over a year ago

And make that query a parameterized one, otherwise it will be just another query vulnerable to SQLi. :)

Erwin Smout Over a year ago

Yes, obviously :-)

Collectives™ on Stack Overflow

Avoiding SQL injection while constructing SELECT

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related