User input should be considered unsafe, since it may lead to SQL injection that may leak, destroy or modify information, thus we must protect against misuse.
For security reasons the least information you release the better off you are.
Thus
Do not allow SQL Injection in column/table names nor reveal them.
// 1.- Best remap your column/table/db names (keys) to safe undisclosed ones
/**
* Change unsafe keys for safe keys
* remapKeys(['id'=>1, 'n'=>'Susan', 'm'=>3], ['id'=>'user_id', 'n'=>'user_name', 'm'=>'friend'])
* [user_id=>1, user_name=>'Susan', 'friend'=>['user_id'=>3]
*
* @param array $inputArray ;
* @param array $remapRules
* @return array
* @require php >= 8.0
*
*/
function remapKeys(array $inputArray, array $remapRules):array {
$remapped = [];
foreach($inputArray as $key => $value)
if(array_key_exists($key, $remapRules) || is_numeric($key))
$remapped[$remapRules[$key]] = is_array($value) ?
remapKeys($value, $remapRules) :
$value;
return $remapped;
}
$in = ['id'=>1, 'n'=>'Susan', 'm'=>['id'=>3]];
$protected = remapKeys($in, ['id'=>'user_id', 'n'=>'user_name', 'm'=>'friend']);
// $protected = [user_id=>1, user_name=>'Susan', 'friend'=>['user_id'=>3] ]
// 2.- Second best validate your column/table/db names, something like
$in = ['user_id'=>1, 'user_name'=>'Susan', 'friend'=>['user_id'=>3], 'injection'=>'evil'];
$protected = array_intersect_key($in, ['user_id'=>'int', 'user_name'=>'string', 'friend'=>'array']);
//3.- If not at least the third best option: Protect your column/table/db names
$sql = "SELECT " . fieldIt($_REQUEST['column_name']) .
" FROM " . fieldIt($_REQUEST['table_name']) .
" WHERE " . fieldIt($_REQUEST['filter_column_name']) . "=?";
/**
* Protect with ` an sql name
* Quotes a: column/table/db name to `column name` respecting . table.column to `table`.`column`
*
* @param string|Stringable $fieldName
* @return string
*/
function fieldIt($fieldName):string {
$protected = [];
$n = explode('.',$fieldName);
foreach($n as $field) {
$protected[]= '`'.str_replace(['`',"\r","\n","\t","\0","\8","\\"], '', trimSuper($field) ).'`';
}
return implode('.', $protected);
}
/**
* Trim a string, or array of strings, to a one liner with single spaces
*
* @param string|Stringable|array $str
* @return:string|array
*/
function trimSuper($str):string|array {
if($str === null)
return '';
if(is_array($str)) {
foreach($str as &$d)
$d = strim($d);
return $str;
}
$s1 = preg_replace('/[\pZ\pC]/muS',' ',$str);
return trim(preg_replace('/ {2,}/muS',' ',$s1));
}