Page 1 of 1
ExecuteScalar() - SQL Best Practice
Posted: Thu Nov 30, 2023 2:16 am
by philmills
I have a few global functions with this basic structure:
function UpdateModuleName($moduleID){
return ExecuteScalar("SELECT Module FROM Updates_Modules WHERE id='".$moduleID."'");
}
Is there any difference in terms of security (sql injections) using this instead?
function UpdateModuleName($moduleID){
$query = ExecuteScalar("SELECT Module FROM ClassAct_Updates_Modules WHERE id='".$moduleID."'");
return $query;
}
Re: ExecuteScalar best practice
Posted: Thu Nov 30, 2023 8:46 am
by mobhar
You should always sanitize the param that will be supplied into your SQL statement.
You may use PHP preg_replace to sanitize it.
In addition, you may use RemoveXss
global function to remove XSS attack from your param.
function UpdateModuleName($moduleID){
$param_moduleID = RemoveXss($moduleID); // remove the XSS if any; just in case
$param_moduleID = preg_replace('/[^a-zA-Z0-9]/', '', $param_moduleID); // only allow a-z, A-Z, 0-9 characters
return ExecuteScalar("SELECT Module FROM Updates_Modules WHERE id='".$param_moduleID."'");
}
Re: ExecuteScalar() - SQL Best Practice
Posted: Thu Nov 30, 2023 10:45 am
by arbei
If the table is from the main database, you may simply use, e.g.
ExecuteScalar("SELECT Module FROM Updates_Modules WHERE id='" . AdjustSql($moduleID) . "'");
Re: ExecuteScalar() - SQL Best Practice
Posted: Mon Mar 11, 2024 11:46 pm
by philmills
Is it allowed to use prepared statements within ExecuteScalar, ExecuteRow, ExecuteQuery etc for sanitization?
// Prepare the SQL query
$query = "SELECT IP FROM Trusted_IPs WHERE IP = :userIp";
// Execute the query with parameter binding
$result = ExecuteQuery($query, ['userIp' => $userIp]);
Re: ExecuteScalar() - SQL Best Practice
Posted: Tue Mar 12, 2024 2:57 am
by yinsw
Both method also works with prepared statement
function UpdateModuleName($moduleID) {
$sql = "SELECT MODULE FROM UPDATES_MODULES WHERE ID = :p1";
$stmt = $conn->prepare($sql);
$stmt->bindValue("p1", $moduleID);
$result = $stmt->executeQuery();
return $result;
}
function UpdateModuleName($moduleID) {
$query = Conn()->createQueryBuilder()
->select("MODULE")
->from("UPDATES_MODULES")
->where("ID=:p1")
//->andWhere("USERID=:p2")
->setParameter("p1", $moduleID);
$result = $query->execute()->fetchOne();
return $result;
}