Add parameters to a PHP mssql query

Question

Given the following query (in the code, NOT a stored procedure); how can I add parameters to the query rather than including the condition values directly in the query? In other words: how can I make this database call secure?


$dbhandle = mssql_connect($myServer, $myUser, $myPass); 
$selected = mssql_select_db($myDB, $dbhandle); 

$query  = "SELECT lastname, firstname, address, phone, email ";
$query .= "  FROM person";
$query .= " WHERE lastname LIKE '" . $lastName . "'";

$result = mssql_query($query);

while($row = mssql_fetch_array($result)) {
... etc.

why use LIKE without % you may as well use =

Lawrence Cherone
– Lawrence Cherone

2011-07-19 08:59:35 +00:00
Commented Jul 19, 2011 at 8:59 — Lawrence Cherone
– Lawrence Cherone, Commented Jul 19, 2011 at 8:59
@Lawrence LIKE will do a case-insensitive match.

Rudi Visser
– Rudi Visser

2011-07-19 09:04:43 +00:00
Commented Jul 19, 2011 at 9:04 — Rudi Visser
– Rudi Visser, Commented Jul 19, 2011 at 9:04

Adam Hopkinson · Accepted Answer · 2011-07-19 08:52:31Z

2

Use PDO to make it secure

http://php.net/manual/en/book.pdo.php

answered Jul 19, 2011 at 8:52

Adam Hopkinson

28.9k9 gold badges74 silver badges105 bronze badges

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

Comments

prodigitalson · Accepted Answer · 2011-07-19 09:07:59Z

First of all abandon the outdated extension and use sqlsrv instead:

These functions allow you to access MS SQL Server database.

This extension is not available anymore on Windows with PHP 5.3 or later.

SQLSRV, an alternative driver for MS SQL is available from Microsoft: » http://msdn.microsoft.com/en-us/sqlserver/ff657782.aspx.

After that you get suppport for prepared statements:

$dbh = sqlsrv_connect ($serverName, $credentials);
$stmt = sqlsrv_prepare($dbh, 'SELECT lastname,firstname,address,phone,email FROM person WHERE lastname LIKE ?', array(&$lastName));


if(sqlsrv_execute($stmt))
{
   while(false !== ($row = sqlsrv_fetch_array($stmt)){
     // do stuff with $row
   }
}

Of course if i were i would just use PDO as others have suggested with presents the same interface to all db the extensions it supports.

If youre stuck using mssql for some reason then i believe youre also stuck manually escaping all your query parameters.

Rudi Visser · Accepted Answer · 2011-07-19 09:11:49Z

What you need is a prepared statement (or just a DAL that will allow you to parameterise your queries!).. One option is to use PDO, specifically with the PDO_SQLSRV driver..

When doing this you can prepare your queries in a parameterised form, and pass them in at query time, for example..

$conn = new PDO("sqlsrv:Server=$myServer;Database=$myDB", $myUser, $myPass);
$stmt = $conn->prepare('SELECT lastname,firstname,address,phone,email FROM person WHERE lastname LIKE ?');
$stmt->execute(array($lastname));
$result = $stmt->fetchAll();
foreach ($result as $row) {
    ...

(note code above should be in an in-place replacement, however it's untested!)

If you have a lot of parameters in your query at some point, it may be best to use named parameters so that you can keep track of them better.

Collectives™ on Stack Overflow

Add parameters to a PHP mssql query

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related