How to escape & and ' in mssql and Php?

Question

I ma pretty sure I need something like a preg_replace in this situation but I am not sure and if so where to put it. I have a page that allows people to search an employee directory (PHP and MSSQL). They can search by last name, building or by department. the last name and building are fine but I have the problem with three of our departments, two have an & in them (ie. Grants & Planning) and when you click on that department it doesn't return any results and I think it is because it is not recognizing the "& planning" as part of a whole string. The other problem I have is that I have one department that has a ' in it and it throws an error

• PHP Warning: mssql_query() [function.mssql-query]: message: Line 1: Incorrect syntax near 's'. (severity 15) in C:\Inetpub\wwwroot\DACC\directory\dept.php on line 179

  *PHP Warning: mssql_query() [function.mssql-query]: message: Unclosed quotation mark before the character string ' ORDER BY Lastname'. (severity 15) in C:\Inetpub\wwwroot\DACC\directory\dept.php on line 179*

Line 179 is this... $query = mssql_query("SELECT * FROM directory WHERE Displayname = '$department' ORDER BY Lastname");

Here is the rest of the code for the query page for by department.... if anyone can help me I would greatly appreciate it! `

$department = $_GET['dept'];

 // This will evaluate to TRUE so the text will be printed.
 if (isset($department)) {
 $query = mssql_query("SELECT * FROM directory WHERE Displayname = '$department' ORDER BY Lastname");//$query = mssql_query("SELECT * FROM directory WHERE department IN (SELECT id FROM departments WHERE name='$department') ORDER BY Lastname");
 $query2 = mssql_query(
 "SELECT TOP 1 directory.FirstName, directory.Lastname, directory.email,
 directory.phone, directory.office, directory.title, directory.displayname,                departments.id AS dept_id, departments.name AS dept_name, departments.url AS dept_url
 FROM directory
 INNER JOIN departments on directory.displayname = departments.name
 WHERE directory.displayname = '$department'
 ORDER BY directory.LastName");
 $numofrows = @mssql_num_rows($query);
 // Check if there were any records
 if (!mssql_num_rows($query)) {
echo 'No records found';
echo '<br /><a href="/directory/">Go Back</a>';
 } else {
 while($row1 = mssql_fetch_array($query2))
   {
  $dept_var = $row1['dept_name'];
 $dept_id = $row1['dept_id'];
 $dept_url = $row1['dept_url'];
    print "<h3><a href=\"$dept_url\">$dept_var</a></h3>";
 }
 print "<table id=\"directory_table\" width=\"480\">
 <tr>
 <th>Name</th>
 <th>Email</th>
 <th>Phone</th>
 <th>Office</th>
 <th>Title</th>
 </tr>";    
 for($i = 0; $i < $numofrows; $i++) 
{
$row = mssql_fetch_array($query);
    if($i % 2) 
    { 
    print '<tr bgcolor="#ffffff">';
    } 
else 
    { 
    print '<tr bgcolor="#eeeeee">';
    }

    print "<td>" . $row['Firstname'] . " " . $row['Lastname'] . "&nbsp;&nbsp;</td>";
    print "<td><a href=\"mailto:" . $row['email'] . "\">" . $row['email']. "</a>&nbsp;&nbsp;</td>";
    print "<td>" . $row['phone'] . "&nbsp;&nbsp;</td>";
    print "<td>" . $row['Office'] . "&nbsp;&nbsp;</td>";
    print "<td>" . $row['Title'] . "&nbsp;&nbsp;</td>";
    print "</tr>";
}
 print "</table>";
 }
 // Free the query result
 mssql_free_result($query);
 }
 else
 print "No Search Defined";
 ?>

EDITED to show changes ok tried this:

    $serverName = "localhost"; //serverName\instanceName
    $connectionInfo = array( "Database"=>"DACC", "UID"=>"daccweb", "PWD"=>"go");
    $conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
 echo "Connection established.<br />";
}else{
 echo "Connection could not be established.<br />";
 die( print_r( sqlsrv_errors(), true));
}
//$conn = sqlsrv_connect("connection string here");
$queryParams = array($department);


//Selector links
print "<a href=\"/directory/\">Go back to main search</a><br />";
print "<u>Search for Employees:</u><br /><br />\n";

print "<br />";





//$officeloc = $_GET['building'];
$department = $_GET['dept'];

// This will evaluate to TRUE so the text will be printed.
if (isset($department)) {


$query = sqlsrv_query($conn, "SELECT * FROM directory WHERE Displayname = ? ORDER BY Lastname", $params);

$query2 = sqlsrv_query($conn, "SELECT TOP 1 directory.FirstName, directory.Lastname, directory.email,
directory.phone, directory.office, directory.title, directory.displayname,
 departments.id AS dept_id, departments.name AS dept_name, departments.url AS dept_url
 FROM directory
INNER JOIN departments on directory.displayname = departments.name
WHERE directory.displayname = ?
ORDER BY directory.LastName", $params);

NEW EDIT
query runs but doesn't echo/print results $query = sqlsrv_query($conn, "SELECT * FROM directory WHERE Displayname = ? ORDER BY Lastname", $params);

$query2 = sqlsrv_query($conn, "SELECT TOP 1 directory.FirstName, directory.Lastname, directory.email,
directory.phone, directory.office, directory.title, directory.displayname,
departments.id AS dept_id, departments.name AS dept_name, departments.url AS dept_url
FROM directory
INNER JOIN departments on directory.displayname = departments.name
WHERE directory.displayname = ?
ORDER BY directory.LastName", $params);


$numofrows = @@sqlsrv_has_rows($query);

// Check if there were any records
if (!@sqlsrv_has_rows($query)) {
echo 'No records found';
echo '<br /><a href="/directory/">Go Back</a>';
} else {



while($row1 = sqlsrv_fetch_array($query2))
  {
    $dept_var = $row1['dept_name'];
    $dept_id = $row1['dept_id'];
    $dept_url = $row1['dept_url'];


   print "<h3><a href=\"$dept_url\">$dept_var</a></h3>";
  //echo "</h3><br />";
    }



    print "<table id=\"directory_table\" width=\"480\">
    <tr>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
<th>Office</th>
<th>Title</th>

</tr>"; 
for($i = 0; $i < $numofrows; $i++) 
{
$row = sqlsrv_fetch_array($query);
    if($i % 2) 
    { 
    print '<tr bgcolor="#ffffff">';
    } 
else 
    { 
    print '<tr bgcolor="#eeeeee">';
    }

    print "<td>" . $row['Firstname'] . " " . $row['Lastname'] . "&nbsp;&nbsp;</td>";
    print "<td><a href=\"mailto:" . $row['email'] . "\">" . $row['email']. "</a>&nbsp;&nbsp;</td>";
    print "<td>" . $row['phone'] . "&nbsp;&nbsp;</td>";
    print "<td>" . $row['Office'] . "&nbsp;&nbsp;</td>";
    print "<td>" . $row['Title'] . "&nbsp;&nbsp;</td>";
    print "</tr>";
}
print "</table>";
}

// Free the query result
sqlsrv_free_stmt($query);
}
else
print "No Search Defined";

Answer 1

You can use SQL parameters in PHP and MSSQL, have a look at this:

http://blogs.msdn.com/b/sqlphp/archive/2008/09/30/how-and-why-to-use-parameterized-queries.aspx

Your parameter values will automatically be escaped without any work on your part.

You'll need to use the sqlsrv driver, see: http://www.php.net/manual/en/sqlsrv.setup.php

In order to get the number of rows we need to specify some query options as well. (Take a look at http://www.php.net/manual/en/function.sqlsrv-num-rows.php and http://msdn.microsoft.com/en-us/library/hh487160.aspx)

$conn = sqlsrv_connect("connection string here");
$queryParams = array($department);
$queryOptions = array( "Scrollable" => "buffered" );
$query = sqlsrv_query($conn, "SELECT * FROM directory WHERE Displayname = ? ORDER BY Lastname", $queryParams, $queryOptions);

$query2 = sqlsrv_query($conn, "SELECT TOP 1 directory.FirstName, directory.Lastname, directory.email,
directory.phone, directory.office, directory.title, directory.displayname,
departments.id AS dept_id, departments.name AS dept_name, departments.url AS dept_url
FROM directory
INNER JOIN departments on directory.displayname = departments.name
WHERE directory.displayname = ?
ORDER BY directory.LastName", $queryParams, $queryOptions);

$numofrows = sqlsrv_num_rows($query);

Note that the order you build your array in must match the order in which the ? symbols appear in the query. As you only use one parameter in each query and they are the same, you only need to build one array.

You would then substitute all your mssql functions with sqlsrv functions, for a list of the functions and their usage, see the docs: http://www.php.net/manual/en/ref.sqlsrv.php