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

[andyrav]

Which is the best way to project from SQL injections?

$result = ExecuteScalar("SELECT Module FROM Updates_Modules WHERE id='" . AdjustSql($moduleID) . "'");

or

$sql  = "SELECT MODULE FROM UPDATES_MODULES WHERE ID = :p1";
$stmt = $conn->prepare($sql);
$stmt->bindValue("p1", $moduleID);
$result = $stmt->executeQuery();

or

$query = "SELECT IP FROM Trusted_IPs WHERE IP = :userIp";
$result = ExecuteQuery($query, ['userIp' => $userIp]);

thanks

[arbei]

If the SQL is simple as in your example, all are the same (in terms of SQL injection prevention). However, if the SQL is more complex, the first approach might lead to complex to maintain SQL queries, see Dynamic Parameters and Prepared Statements. The 2nd argument of ExecuteQuery() is not parameters, you need to handle the SQL as in the first approach, then the 2nd and 3rd approach are the same, only in different syntax.

[andyrav]

Thank but way does this command not work?

$query = "SELECT IP FROM Trusted_IPs WHERE IP = :userIp";
$result = ExecuteQuery($query, ['userIp' => $userIp]);

do you have to format it like

$sql = "SELECT * FROM users WHERE name = :name OR username = :name";
$stmt = $conn->prepare($sql);
$stmt->bindValue("name", $name);
$resultSet = $stmt->executeQuery();

[arbei]

The second argument of ExecuteQuery() is not parameters, see ExecuteQuery($sql [,$dbname]).

If you want to use SQL paramters, you should use Conn() to get the connection and then execute $conn->executeQuery().