在SQL查询中转义特殊字符

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>

";

 }
doubiao7410
doubiao7410 Inky:njk有一个观点。填充列表时,您应该同时获得id和名称。这样,当您查询目录表时,您已经拥有了id。
大约 8 年之前 回复
dongshuanggu4333
我在西湖1 它从两个不同的表中拉出来,id只是一个四字母代码,用于内部目的
大约 8 年之前 回复
doufen3091
doufen3091 你有什么理由不能通过departments.id选择部门吗?
大约 8 年之前 回复
dongqiang5865
dongqiang5865 不是问题-我们这里都是新来的:)
大约 8 年之前 回复
dourunlao1642
dourunlao1642 对不起还是新手。
大约 8 年之前 回复
douhoulei4706
douhoulei4706 请不要在评论中发布代码-更新您的问题并将其发布到那里-如您所见,所有格式都丢失了。
大约 8 年之前 回复
drau67562
drau67562 这是生成部门链接函数listDepts(){$query=“SELECTDISTINCTnameFROMdepartmentsORDERBYname”的代码;$result=mssql_query($query);echo“<h3>请选择一个部门:</h3> ”;echo“<ul> ”;for($i=0;$i<mssql_num_rows($result);$i++){$info=mssql_fetch_assoc($result);echo“<li><ahref=\"dept.php?dept=$info[name]\">$info[name]</a></li> ”;}echo“</ul> ”;}
大约 8 年之前 回复
doutou7549
doutou7549 你是如何实际执行查询的?你是如何传递参数的?
大约 8 年之前 回复
douduxia1551
douduxia1551 当它从桌子上拉出来时,就会出现'部门名称女子垒球'的问题
大约 8 年之前 回复
dpq755012465
dpq755012465 你在哪里看到一个SQL特殊字符?
大约 8 年之前 回复

1个回答



我强烈建议您使用预准备语句,然后使用变量执行它:</ p>

   $ stmt = $ dbh-&gt; prepare(“SELECT * FROM目录WHERE部门IN(SELECT id FROM departments WHERE name =?)ORDER BY Lastname”); 
if($ stmt-&gt; execute(array(“ 女子垒球教练“))){
while($ row = $ stmt-&gt; fetch()){
print_r($ row);
}
}
</ code> </ pre> \ n

有关详细信息,请参阅关于准备好的声明的PHP文档 。</ p>

在你的具体情况下,你会有这样的事情:</ p>

  $ stmt = $ dbh-&gt; prepare(“  SELECT * FROM目录WHERE部门IN(SELECT id FROM departments WHERE name =?)ORDER BY Lastname“); 
for($ i = 0; $ i&lt; mssql_num_rows($ result); $ i ++){
if($ stmt) - &gt; execute(array($ result))){

$ info = $ stmt-&gt; fetch();

...
}
</ code> </ pre>
</ div>

展开原文

原文

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

dsx58940
dsx58940 试图让它工作,没有成功,但会继续努力。
大约 8 年之前 回复
doubi1910
doubi1910 看到我更新的答案。 您准备一次查询 - 然后在具有重复参数的循环中执行它。
大约 8 年之前 回复
dongqia0240
dongqia0240 谢谢,这应该超前查询?
大约 8 年之前 回复
douxian3170
douxian3170 它适用于所有数据。 看到我更新的答案。
大约 8 年之前 回复
douzi8112
douzi8112 问题是它不会只照顾那个部门。 如果有其他部门有特殊字符,我仍然会遇到同样的问题。
大约 8 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问