如何查询包含单引号的数据库字段? [英] How do you query a database field containing single quotes?

问题描述

我正在使用Classic ASP.我知道有一条记录与我的简单SQL选择查询匹配.它具有'字符'.代码如下:

I am working in Classic ASP. I know there is a record that matches my simple SQL select query. It has the ' character ' in it. The code is as follows:

Fieldname = Replace(trim(Request.form("Fieldname")),"'","'", 1, 10)  
'replace the "'" up to 10 times with the ' code to avoid SQL issues, LOL.

SQL = "select id,fieldname from table where fieldname='"&Trim(Fieldname)&"'"

set rs = server.createobject("adodb.recordset")
rs.open SQL, Application("conn"), 1, 1
If not rs.eof then
    response.redirect "somepage.asp?QS=Fieldname_Exists_in_DB"
Else
    'Sample.hold the value in a hidden input field and pass it to the next page
End If

问题是，我知道一个事实，即字段名称和字段名称值在MS-SQL 2016服务器表中.我一直都从中提取数据.与SQL数据库字段进行比较时，数据库字段中的值包含'值，就像替换的表单字段名"一样，因此，它不应通过IF NOT RS.EOF问题.然而它每次都会过去.

The problem is, I know for a fact the fieldname and fieldname value is in the MS-SQL 2016 server table. I pull data from it all the time. The value in the database field contains the ' value as does the Replaced FORM Fieldname when it is compared to the SQL database field, so it should NOT pass the IF NOT RS.EOF question. Yet it passes every time.

我想念什么?我正在这个完全相同的应用程序的其他地方进行完全相同的查询，其行为与人们期望的一样.

What am I missing? I'm doing the exact same query in other places on this exact same app and it behaves as one would expect.

推荐答案

试图在注释中进行解释，但是由于遗漏了重点，我将在此处尝试举一个例子.

Tried to explain in the comments but as the point is being missed, I'll try to give you an example here.

经典ASP服务器端代码没有任何经过清理的数据概念.这意味着任何来自用户通过Request对象(如Request.Form("Fieldname")) 的输入都是不可信的. . >

Classic ASP server-side code that interacts with the ADODB Library doesn't have any notion of sanitised data. This means that any input that comes from the user via the Request object (like Request.Form("Fieldname")) should not be trusted.

Fieldname = Replace(trim(Request.form("Fieldname")),"'","'", 1, 10)
SQL = "select id,fieldname from table where fieldname='"&Trim(Fieldname)&"'"

此示例对 SQL注入攻击开放，通常是不良做法，并且会导致安全性可以通过Internet上可用的脚本工具轻松利用这些缺陷.

This example is open to SQL Injection attacks and is generally bad practise and leads to security flaws that can be easily exploited with script tools readily available on the internet.

除了引入的安全缺陷外，由于需要构造SQL调用字符串和其他数据类型的方式(因提供程序而异)，这也使得查询数据更加困难.必须考虑可能被视为危险或可能破坏查询的字符的各种组合，这是一项繁琐的任务，而当ADODB已经有解决方案时，在野外经常会发现这一问题.

Apart from the security flaws introduced, it also makes it harder to query data due to how SQL calls for strings and other data types need to be constructed (which varies from provider to provider). Having to account for the various combinations of characters that could be deemed dangerous or likely to break the query can be a cumbersome task and one seen far too often in the wild when ADODB already has a solution.

ADODB库具有一个名为

The ADODB Library has an in-built object called ADODB.Command which takes all these hassles away.

使用问题中的示例，可以编写相同的查询，而无需手动清理数据或直接针对用户输入执行SQL.

Using the example in the question the same query can be written without the failings of manually sanitising data or executing SQL directly against user input.

Const adCmdText = 1
Const adVarWChar = 202
Const adParamInput = 1

Dim Fieldname, SQL, cmd, rs, 
Fieldname = Trim(Request.Form("Fieldname") & "")

SQL = "SELECT id, fieldname FROM table WHERE fieldname = ?"

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
  .ActiveConnection = Application("conn")
  .CommandType = adCmdText 'Also can use 1
  .CommandText = SQL
  Call .Append(.CreateParameter("@fieldName", adVarWChar, adParamInput, 255))
  Set rs = .Execute(, Array(Fieldname))
End With
Set cmd = Nothing

If Not rs.EOF then
    response.redirect "somepage.asp?QS=Fieldname_Exists_in_DB"
Else
    'Sample.hold the value in a hidden input field and pass it to the next page
End If

---

有用的链接

• A:使用METADATA导入DLL常量 (显示了使用命名常量的方法需要在代码中添加您自己的Const声明).

---

Useful Links

• A: Using METADATA to Import DLL Constants (shows an approach to using Named Constants that doesn't require adding your own Const declarations to the code).

这篇关于如何查询包含单引号的数据库字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！