| JohnHasher |
12th December 2021 17:13 |
SQL prepared statement for TT
now you can use prepared statements to avoid sql injection for people who still use mysqli instead of PDO.
find and replace the code
change following in mysql.class.php
PHP Code:
function &SQL_Query_exec ($query,$bind = '',$iden = '') {
$sql = new SQL_Query($query,$bind,$iden);
return $sql->execute();
}
class SQL_Query {
private $query = "";
private $params = array();
private $bind;
function &__construct ($query,$bind,$iden) {
$this->query = $query;
$this->bind = $bind;
$this->iden = $iden;
return $this;
}
function &p ($param) {
if (is_numeric($param)) {
$this->params[] = $param;
} elseif (is_array($param)) {
$this->params[] = implode(", ", array_map(array(&$this, "escape"), $param));
} else { $this->params[] = $this->escape($param); } return $this; }
function &p_name ($param) {
$this->params[] = "`".$GLOBALS["DBconnector"]->real_escape_string($param)."`";
return $this;
}
function escape ($s) {
if(is_numeric($s)){
return $s;
}else{
return "'".$GLOBALS["DBconnector"]->real_escape_string($s)."'";
} }
function read () {
$ret = "";
if (count($this->params)) {
reset($this->params);
for ($i = 0; $i < strlen($this->query); $i++) {
if ($this->query[$i] == "?") {
list(, $val) = thisEach($this->params);
$ret .= $val;
} else {
$ret .= $this->query[$i];
} }
reset($this->params);
} else {
$ret = $this->query;
}
return $ret;
}
function &execute() {
global $db_debug;
$par = $this->bind; //in array
if(empty($par)){
$query = $this->read();
$res = $GLOBALS["DBconnector"]->query($query);
}else{ $query = $this->query;
$iden = $this->iden;
$stmt = $GLOBALS["DBconnector"]->prepare($query);
if(empty($iden)){
$iden = str_repeat("s",count($par));
}else{ }
$stmt->bind_param($iden, ...$par);
$stmt->execute();
$res = $stmt ->get_result();
}
$mysqli_error = $GLOBALS["DBconnector"]->error;
$mysqli_errno = $GLOBALS["DBconnector"]->errno;
if ($res || $mysqli_errno == 1062) { return $res; } // If debug_backtrace() is available, we can find exactly where the query was called from
if (function_exists("debug_backtrace")) {
$bt = debug_backtrace();
$i = 1;
if ($bt[$i]["function"] == "SQL_Query_exec_cached" || $bt[$i]["function"] == "get_row_count_cached" || $bt[$i]["function"] == "get_row_count")
$i++;
$line = $bt[$i]["line"];
$file = str_replace(getcwd().DIRECTORY_SEPARATOR, "", $bt[$i]["file"]);
$msg = "Database Error in $file on line $line: $mysqli_error. Query was: $query.";
} else {
$file = str_replace(getcwd().DIRECTORY_SEPARATOR, "", $_SERVER["SCRIPT_FILENAME"]);
$msg = "Database Error in $file: $mysqli_error. Query was: $query";
}
$GLOBALS["DBconnector"]->query("INSERT INTO `sqlerr` (`txt`, `time`) VALUES (".sqlesc($msg).", '".get_date_time()."')");
// mysqli_query($GLOBALS["DBconnector"],"INSERT INTO `sqlerr` (`txt`, `time`) VALUES (".sqlesc($msg).", '".get_date_time()."')");
if($db_debug=="true"){
if ( function_exists('show_error_msg') ){ show_error_msg("Database Error", "Database Error. Please report this to an administrator.", 1); }
}
}
}
replace the old mysqli procedural to Object Oriented
FILE : functions.php
PHP Code:
function_exists("mysqli_connect") or die("MySQLi support not available.");
$GLOBALS["DBconnector"] = new mysqli($mysql_host, $mysql_user, $mysql_pass,$mysql_db); // Check connection
if ($GLOBALS["DBconnector"]->connect_error) {
die("Connection failed: " . $GLOBALS["DBconnector"]->connect_error);
}
how to use:
PHP Code:
$q = SQL_Query_exec("SELECT username FROM users WHERE id=?",['1']);
remember parameters should be in array, even for 1 parameter.
default identifier is string,if you like to mention any other identifier like integer.
PHP Code:
$q = SQL_Query_exec("SELECT username FROM users WHERE id=?",['1'],"i");
for multiple identifier: example "iii"; (means 3 identifiers for 3 parameters)
greetings to people who helped me :friend: |
