Parameterized SQL Columns?

parameterized, sql-injection

Solution

Instead of passing the column names, just pass an identifier that you code will translate to a column name using a hardcoded table. This means you don't need to worry about malicious data being passed, since all the data is either translated legally, or is known to be invalid. Psudoish code:

@columns = qw/Name Address Telephone/;
if ($columns[$param]) {
  $query = "select * from contacts where $columns[$param] = ?";
} else {
  die "Invalid column!";
}

run_sql($query, $search);

Problem

I have some code which utilizes parameterized queries to prevent against injection, but I also need to be able to dynamically construct the query regardless of the structure of the table. What is the proper way to do this? Here's an example, say I have a table with columns Name, Address, Telephone. I have a web page where I run Show Columns and populate a select drop-down with them as options. Next, I have a textbox called Search. This textbox is used as the parameter. Currently my code looks something like this: ``` result = pquery('SELECT * FROM contacts WHERE `' + escape(column) + '`=?', search); ``` I get an icky feeling from it though. The reason I'm using parameterized queries is to avoid using escape. Also, escape is likely not designed for escaping column names. How can I make sure this works the way I intend? Edit: The reason I require dynamic queries is that the schema is user-configurable, and I will not be around to fix anything hard-coded.

Original source