Currently, I have a setup so that when a MySQL query is made in PHP, I call this function:
getfromtable("table_name",array("field1","field2",...),"something='value' and something2='".$_GET['INPUT']."' and something3=\"".$_GET['INPUT2']."\" and count=3");
That function then arranges the parameters and calls this function:
$result=mysqli_query("SELECT field1,field2 FROM table_name where something='value' and something2='".$_GET['INPUT']."' and something3=\"".$_GET['INPUT2']."\" and count=3;",MYSQLI_USE_RESULT);
This in turn means this SQL statement:
SELECT field1,field2 FROM table_name where something='value' and something2='<user input 1>' and something3="<user input 2>" and count=3;
where <user input 1>
and <user input 2>
are two values directly from the query string.
The problem is that this is open to SQL injection. Another problem is its amplified nearly everywhere in my code because there are at least 290 spots in my application where that function is used.
What one would likely suggest to me is to sanitize each input right away and use it so then my function call would be similar to:
getfromtable("table_name",array("field1","field2",...),"something='value' and something2='".mysql_real_escape_string($_GET['INPUT'])."' and something3=\"".mysql_real_escape_string($_GET['INPUT2'])."\" and count=3");
But the problem is I have to look for the function 290 times and look for the variables to fix which is time consuming.
What I want to do instead is in my function, I want to take the condition and check to see if the SQL statement is properly escaped and escape it if it is not.
This is the function I use from a php class, and its one I made months ago:
function getfromtable($tname,$tparams,$condition){
unset($retres);$params="";
foreach ($tparams as $n){$params=$params.$n.",";}
$params=substr($params,0,-1);// strip last comma to prevent SQL error
if ($condition){$condition=" where ".$condition;}
// $condition=???? //need to fix mysql injection attack
$q=mysqli_query($this->conn,"SELECT ".$params." FROM ".$tname.$condition.";",MYSQLI_USE_RESULT);
if (mysqli_error($this->conn)){echo "query error: ".mysqli_error($this->conn)."<br>";}else{$retres=$this->parsemultiple($q);}
return $retres;
}
the this->parsemultiple()
only produces the results from the query and is not relevant to the question.
I thought of using str_replace, but I'm not sure how to approach this since I have strings in single quotes and strings in double quotes and just using mysqli_real_escape functionality wouldn't make sense on the entire string or the quotes meant to open and close the string will be treated as literals and mysql errors would result.
Is there some way I can do this programmatically inside my getfromtable function where I can add a line or two just before the mysqli_query call that formats the entire query condition so no mysql injection results from bad user input?