ExecuteScalar() - SQL Best Practice

[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;
}

[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."'");
}

[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) . "'");

[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]);

[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;
}