Prevent SQL Injection using sqlsrv in PHP

Question

I am very new to the whole PHP/MSSQL coding and need assistance with SQL Injection prevention.

I am adding a simple search feature to a website that uses a MSSQL database. The code works as I want it to but it is vulnerable to SQL Injection.

Any other way to secure it except for prepare statements?

I am also not that familiar with stored procedures.

I have tried prepare statements with no luck(unless I'm doing something wrong, that's most likely)

Stored procedures I'm not familiar with.

<?PHP

$tech = (isset($_POST['Technician'])? $_POST['Technician'] : null);

$sql = "SELECT * FROM Errors WHERE Error LIKE '%$tech%' or Description LIKE '%$tech%'";

$name = trim($_POST['Technician']);

if(empty($name)){
    print '<script type="text/javascript">alert("Please enter an Error Code or Error Description")</script>';
    exit;
}

$stmt = sqlsrv_query($conn, $sql);

if ($stmt) {
    $rows = sqlsrv_has_rows( $stmt );

    if ($rows === true) {
        echo "";
    } else{
        echo '<script type="text/javascript">alert("Please enter a valid Term")</script>';
    }
}  

while($db_field = sqlsrv_fetch_array($stmt)){
    print '<table align="center" style="position: relative; width:250px; text-align: center;">';
    print '<tr>';
    print '<td><a href="result.php?Error=' . $db_field['Error'] . '">'.$db_field['Error']."</a></td></tr>";
    print "<tr>";
    print '<td>'.$db_field['Description'].'</td></tr>';
    //print "<tr><th>"."Cause"."</th>";
    //print "<td>".$db_field['Cause']."</td></tr>";
    //print "<tr><th>"."Resolution"."</th>";
    //print "<td>".$db_field['Resolution']."</td></tr>";
    print "</table><br>";
}

sqlsrv_close($conn);

?>

I expect SQL Injection to fail when attempted.

"Any other way to secure it except for prepare statements?" Not really. What's the issue with using prepared statements? If you tried and failed, please show us and explain what goes wrong, then we can help you fix it. Note that using prepared statements does not require you to use stored procedures, but equally using stored procedures doesn't remove the need to use prepared statements. — ADyson
– ADyson, Commented Jan 14, 2019 at 21:28
@DewD to get code output like that add 4 spaces before each line. I've edited your question to format it — WOUNDEDStevenJones
– WOUNDEDStevenJones, Commented Jan 14, 2019 at 21:29
"I have tried prepare statements with no luck" You should show this attempt and describe the result/errors. — Patrick Q
– Patrick Q, Commented Jan 14, 2019 at 21:29

AbraCadaver · Accepted Answer · 2019-01-14 21:37:22Z

6

Pretty simple to use a parameterized query, notice only ?:

$sql = "SELECT * FROM Errors WHERE Error LIKE ? OR Description LIKE ?";

Then build an array of parameters adding the LIKE wildcards %:

$params = array("%$tech%", "%$tech%");

Execute with parameters:

$stmt = sqlsrv_query( $conn, $sql, $params);

Alternatively, for flexibility with other queries, for the parameters you could do:

$tech = "%$tech%";
$params = array($tech, $tech);

answered Jan 14, 2019 at 21:37

AbraCadaver

79.2k7 gold badges75 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DewD Over a year ago

It was that simple?

ADyson Over a year ago

@DewD yep :-). There are two different pieces of official documentation on the topic as well... the PHP one and the Microsoft one as well as this old SO answer specifically showing how to use them with LIKE, similar to this answer

DewD Over a year ago

Thank you for the assistance everyone. Greatly appreciated.

Álvaro González · Accepted Answer · 2019-01-15 12:55:33Z

Prepared statements are the simplest way to inject raw input into SQL. You can skip them altogether if you want but it comes at the price of greater complexity and lower security (and you need to write the alternative code yourself). The only reason why you can find escape() functions in other extensions (or no mechanism at all!) is because they're legacy libraries, sometimes very old ones. SQLSRV is reasonably modern.

Additionally, you may want to:

User filter functions to simply input checks.
Escape wildcard characters.

Resulting code would look like this:

$tech = filter_input(INPUT_POST, 'Technician');
if ($tech !== null) {
    $sql = "SELECT *
        FROM Errors
        WHERE Error LIKE ? or Description LIKE ?";
    $find = '%' . escapeLike($tech) . '%';
    $params = [$find, $find];
    $res = sqlsrv_query($conn, $sql, $params);
}

function escapeLike($value)
{
    return strtr($value, [
        '%' => '[%]',
        '_' => '[_]',
        '[' => '[[]',
    ]);
}

Collectives™ on Stack Overflow

Prevent SQL Injection using sqlsrv in PHP

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related