动态查询和安全选项

Question

I have made simple and thin database layer to help me with normal operation. Now that it is working, I have decided to scrutinize it security-wise. The first bump (with help of Mordred) was my dynamic query. The first problem variable for a table was easily resolved by validating a table variable against fixed regex:

$regex = '/^[a-zA-Z0-9_$]+$/';

Now, the next variable to sanitize/validate is columns names and AFAICS is I have two options. First is retrieve all columns in that table and build whitelist or use again regex for validating column variable. My Question is, which one of the above is a way to go? Is it good to use regex validation for a tablename? Below is a sample SQL string I'm talking about

$stmt= $this->conn->prepare("SELECT * FROM $table WHERE $id_col[0]=:id");            
$stmt->execute(array(":id"=>$id_val[0]));
$this->resultset = $stmt->fetch(PDO::FETCH_ASSOC);  

Hope to hear from you friends (Sorry if the question is answered somewhere. I could not find anything of this sort!)

Answer 1

What I did is create a script that scans all tables and creates whitelist for table names and columns then I use that to validate any user input that is supposed to be table/column name since they don't go into parametric query. Anything else is parameterized via PDO Bind!