是否有必要在提交SQL查询时验证列名？

Question

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

Answer 1

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.