Avoid SQL Injection in PHP file to a Firebird Database

Question

I have the following code in a PHP which works, but I suspect it susceptible to SQL injection. Am I right? The query is to a Firebird 2.1 database, not MySQL.

$timesheetBatchNo = '221706';
$query = "SELECT DATEWORKED, ORDERTYPE, REFERENCENUMBER, REFERENCEDESCRIPTION, TASKORSTEPNAME, HOURSWORKED, RECORDEDNOTES, ADDITIONALFIELD_1 as EU_REFERENCENUMBER, ADDITIONALFIELD_2 AS EU_TASKORSTEP, ADDITIONALFIELD_3 AS RDACTIVITY";
$query .= " FROM TIMESHEETLINES ";
$query .= " WHERE TIMESHEETBATCHNO=$timesheetBatchNo ";

I believe instead of including $employeename in the SQL string, I need to parse the variable into the query.

I understand I should be able to do this via: ibase_bind_param($query , 1, $employeename); or ibase_execute($query, $employeename); or $stmt->bindValue(':empname', $employeename); but none of these are working right now.

Answer 1

Yes, you're correct. The code you provided is susceptible to SQL injection because it directly includes the $timesheetBatchNo variable in the SQL string without proper sanitization or parameter binding.

You should use parameterized queries or prepared statements.

$employeename = 'ALLSOPPC';
$query = "SELECT DATEWORKED, ORDERTYPE, REFERENCENUMBER, REFERENCEDESCRIPTION, TASKORSTEPNAME, HOURSWORKED, RECORDEDNOTES, ADDITIONALFIELD_1 as EU_REFERENCENUMBER, ADDITIONALFIELD_2 AS EU_TASKORSTEP, ADDITIONALFIELD_3 AS RDACTIVITY";
$query .= " FROM TIMESHEETLINES ";
$query .= " WHERE TIMESHEETBATCHNO = ?";

// Assuming you have a MySQLi connection established
$stmt = $mysqli->prepare($query);
$stmt->bind_param('i', $timesheetBatchNo); // Assuming TIMESHEETBATCHNO is an integer, adjust the type accordingly if it's a different data type
$timesheetBatchNo = $timesheetBatchNo; 
$stmt->execute();

Answer 2

As luukd has already confirmed, your current code is vulnerable to SQL Injection.

Most of the ibase_* functions return false on failure, so you can check and return the ibase_errormsg(). This is a crude example, and you should not blindly return error messages in production as they can give hints as to possible attack vectors:

<?php

$dbh = ibase_connect($host, $username, $password);
if ($dbh === false) {
    trigger_error(ibase_errormsg());
}

$query = <<<SQL
    SELECT DATEWORKED, ORDERTYPE, REFERENCENUMBER, REFERENCEDESCRIPTION, TASKORSTEPNAME, HOURSWORKED, RECORDEDNOTES, ADDITIONALFIELD_1 as EU_REFERENCENUMBER, ADDITIONALFIELD_2 AS EU_TASKORSTEP, ADDITIONALFIELD_3 AS RDACTIVITY
    FROM TIMESHEETLINES
    WHERE TIMESHEETBATCHNO = ?
SQL;

$stmt = ibase_prepare($dbh, $query);
if ($stmt === false) {
    trigger_error(ibase_errormsg());
}

$timesheetBatchNo = 123;
$result = ibase_execute($stmt, $timesheetBatchNo);
if ($result === false) {
    trigger_error(ibase_errormsg());
} else {
    // handle result
    while ($row = ibase_fetch_object($result)) {
        // do some stuff
    }
}

You may want to try the Firebird PDO driver but I do not know whether it will work with your old version.