Hello, everyone,
I’ve got a CFC that contains a function that will query a table in my database, but allows a customized list of column names to SELECT. For example, let’s say TableA has ColA, ColB,… ColZ, and the user only wants/needs ColA, ColD, and ColM. The query is currently:
SELECT #arguments.fieldList# FROM TableA
Now, anyone who has been working with databases knows to guard against SQL-injection. If the dynamic variables were value being inserted, I can use CFQUERYPARAM to protect my stuff. But CFQUERYPARAM will NOT work for column names.
So, how can I protect my dynamically set column names from attack?
V/r,
Different databases have different delimiters for schema, table, and column names. For instance, the backtick in MySQL:
SELECT `id`, `name`, `type` FROM `items`
or square braces in SQLite:
SELECT [id], [name], [type] FROM [items]
Your database should have something similar. Check the documentation.
So, I’m assuming these are coming from the client?
Sounds like you just need to sanitize the fields yourself using a regex. Is this an obscenely large list of tables/columns? If not, map them to an object using a switch.
I remember doing this once. I don’t remember how I did it, tbh.
Different databases have different delimiters for schema, table, and column names. For instance, the backtick in MySQL:
SELECT `id`,`name`,`type` FROM `items`
@diggy_dude
`; DROP TABLE USERS;
The backticks aren’t coming from the client. They’re in the string that you want to dynamically put a table name into. In that case, the attack would be foiled:
SELECT `id`,`name`,`type` FROM `items;DROP TABLE users`
In any case, you definitely don’t want the user to be able to enter an arbitrary table name. They could still circumvent the backticks by inserting more backticks. Let’s say the user enters:
items;`DROP TABLE `users
Then you have:
SELECT `id`,`name`,`type` FROM `items`;DROP TABLE `users`
That’s what he was asking for a way to select the table & columns from the client.
SELECT `id`,`name`,`type` FROM `#clientvar#`
Which I think the only good way to do it, would be to have a prebuilt enum type object with the values. I stopped in CF7 and only used CF9 for like 2 weeks before I left that job, so I don’t know what options are available now. (and I’m pretty rusty with it too tbh)
That or storing them in all in a table or maybe some SQL Magic to dynamically get the tables and columns based on some criteria. Anything to get the actual variables out of the hands of the client.
@mawburn Your original advice to sanitize user input makes sense in every case.
I was kind of afraid that manually sanitizing the column names might be the only (or best) solution. Hmm…