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!)
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!
function escapeIdent($str) {
return "`".str_replace("`","``",$str)."`";
}
However, I would advise against both escaping and such a bulk whitelisting from the other answer.
The whitelist ought to be not site-wide, but specific to the very statement it is used for.
Although a user-supplied field name may contain no injection code, it can be still of some danger anyway.
Say, if an attacker managed to substitute some harmless field name like surname with more important one like access level, he can gain admin rights on the site.