mysql查询包含单引号,如何查询包含单引号的数据库字段？

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

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.

Do not trust user input

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)&"'"

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.

Manually sanitising data

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.

Parameterised Queries

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

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

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).