CODEWITHSUNDEEP
phpmysqlpdosql-injection
asdf
asdf

Reputation: 357

Is it necessary to validate column names when submitting an SQL Query?

In my SQL Queries I am submitting data from forms filled out by the user, and as shown here it is not possible to parameterize my column names with PDO. This is important because the column names in the query are inserted dynamically based on the field names in the form.

I can rather easily validate the column names submitted in the $_POST array by simply pulling them out of the database and throwing out any that don't match. Is this a good thing to do to avoid SQL injection or is simply a waste of system resources (as it effectively doubles the execution of any request that relies on the Database)?

Upvotes: 0

Views: 297

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157900

Is this a good thing to do to avoid SQL injection

No.

or is simply a waste of system resources

No.

It cannot be a waste as it's just a simple select from the system table.

But it is still can be a some sort of injection when a user isn't allowed to some fields. Say, if there is an (imaginary) field "user_role" filled by site admin and a user will have a possibility to define it in the POST, they can alter their access privileges.

So, hardcoding (whitelisting) allowed fields is the only reliable way.

as it effectively doubles the execution of any request that relies on the Database

Man. Databases intended to be queried. It's the only their purpose. A database that cannot sustain a simple select query is a nonsense. Queries are different. An insert one is way more heavy than 10 selects. You have to distinguish queries by quality, not quantity.

the column names in the query are inserted dynamically based on the field names in the form.

Though for the insert/update queries it is quite true, for the SELECT ones it is a BIG SIGN of the bad design. I can stand variable field names in the WHERE/ORDER BY clauses but if you have to vem in the fieldset of table name clauses - your database design is wrong for sure.

Upvotes: 1

DRapp
DRapp

Reputation: 48169

Aside from hard-coding the list of columns, you could build a list of columns via another table in your database that you want to allow column querying from, such as

QuerableSources
SrcTable      SrcColumn      DescriptToUser
SomeTable     SomeColumn     Column used for
AnotherTable  AnotherColumn  Something Else
etc.

Then, you build for example a combobox for a user to pick the "DescriptionToUser" content for easier readability, and YOU control the valid column and table source.

As for the VALUE they are searching for, DEFINITELY Scrub / clean it to prevent SQL-Injection.

Upvotes: 1

vissi
vissi

Reputation: 2334

You can hard-code the column names to make it faster. You can also cache the pulled table description, so that you don't need to update the code every time table schema changes.

Upvotes: 0

Related Questions