在SQL查询中转义特殊字符

Question

I have a SQL Server table with department names in it (I.e. Admissions & Registration, Women's Softball coach) and when you click a link on our page it pulls all employees under that department however when you pull the Women's Softball coach I get an error as below:

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

PHP Warning: mssql_query() [function.mssql-query]: >Query failed in C:\Inetpub\wwwroot\DACC\directory\dept.php on line 179

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

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

I know this is an issue with escaping special characters but is there a way to do that in the query or do I have to do it in the table?

The code referenced above is here--->

$department = $_GET['dept'];

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

 // Send a select query to MSSQL

$query = mssql_query("SELECT * FROM directory WHERE department IN (SELECT id FROM     departments WHERE name='$department') ORDER BY Lastname");

Here is how the query is executed:

   function listDepts() { 

    $query = "SELECT DISTINCT name FROM departments ORDER BY name"; 
    $result = mssql_query($query); 
    echo "<h3>Please select a department:</h3>
"; 
    echo "<ul>
"; 

    for ($i=0; $i<mssql_num_rows($result); $i++) { 
        $info = mssql_fetch_assoc($result); 
        echo "<li><a href=\"dept.php?dept=$info[name]\">$info[name]</a></li>
"; 
    } 

    echo "</ul>

"; 
}

Here is the code that generates the department list.

 function listDepts() {

$query = "SELECT DISTINCT  name FROM     departments ORDER BY     name";
$result = mssql_query($query);

echo "<h3>Please select a department:</h3>
";
echo "<ul>
";

for ($i=0; $i<mssql_num_rows($result); $i++) {
    $info = mssql_fetch_assoc($result);
    echo "<li><a href=\"dept.php?dept=$info[name]\">$info[name]</a></li>
";
}

echo "</ul>

";

 }

Answer 1

I would strongly suggest that you use prepared statement and then execute it using the variable:

$stmt = $dbh->prepare("SELECT * FROM directory WHERE department IN (SELECT id FROM departments WHERE name=?) ORDER BY Lastname");
if ($stmt->execute(array("Women's Softball coach"))) {
    while ($row = $stmt->fetch()) {
        print_r($row);
    }
}

See PHP documentation on prepared statement for more info.

In your specific case, you'd have something like this:

$stmt = $dbh->prepare("SELECT * FROM directory WHERE department IN (SELECT id FROM departments WHERE name=?) ORDER BY Lastname");
for ($i=0; $i<mssql_num_rows($result); $i++) {
    if ($stmt->execute(array($result))) {

        $info = $stmt->fetch(); 
        ...
}