Escaping special characters in SQL query

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>\n"; 
    echo "<ul>\n"; 

    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>\n"; 
    } 

    echo "</ul>\n\n"; 
}

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>\n";
echo "<ul>\n";

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>\n";
}

echo "</ul>\n\n";

 }

when it pulls from the table it is having issue with a ' in the department name Women's Softball — Inky1231
– Inky1231, Commented Aug 28, 2012 at 13:54
How are you actually executing the query? How are you passing in the parameter? — Oded
– Oded, Commented Aug 28, 2012 at 13:56
Please don't post code in comments - update your question and post it there - as you can see, all formatting is lost. — Oded
– Oded, Commented Aug 28, 2012 at 14:00
Is there any reason you can't select the department by departments.id? — Kermit
– Kermit, Commented Aug 28, 2012 at 14:24

Aleks G · Accepted Answer · 2012-08-28 14:06:15Z

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(); 
        ...
}

edited Aug 28, 2012 at 14:06

answered Aug 28, 2012 at 14:00

Aleks G

57.5k34 gold badges178 silver badges280 bronze badges

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

4 Comments

Inky1231 Over a year ago

the problem with that is wouldn't it only take care of that one department. If there are other departments with special characters I would still have the same problem.

Aleks G Over a year ago

@Inky1231 It would work for all data. See my updated answer.

Aleks G Over a year ago

@Inky See my updated answer. You prepare the query once - and then execute it in a loop with repeated parameters.

Inky1231 Over a year ago

trying to get it to work, no success yet but will keep trying.

Collectives™ on Stack Overflow

Escaping special characters in SQL query

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related