asp 在mysql 查询,MySQL的/经典的ASP - 参数化查询

In an absolute emergency, I am trying to go through my website and add parameterized queries. I'm a newbie and have only just learnt about them.

My problem is, I only know a very little about connection types and all of the examples I'm seeing are using another methods of connection, which is confusing me. I don't particularly want to change the way I connect to my DB, as it's on lots of pages, I just want to update my queries to be safer.

This is how I have been connecting to my DB:

Set connContent = Server.CreateObject("ADODB.Connection")

connContent.ConnectionString = "...blah...blah...blah..."

connContent.Open

and this is the SQL bit with parameters:

username = Trim(Request("username"))

connContent.Prepared = True

Const ad_nVarChar = 202

Const ad_ParamInput = 1

SQL = " SELECT * FROM users WHERE (username=?) ; "

Set newParameter = connContent.CreateParameter("@username", ad_nVarChar, adParamInput, 20, username)

connContent.Parameters.Append newParameter

Set rs = connContent.Execute(SQL)

If NOT rs.EOF Then

' Do something...

End If

rs.Close

It's obviously not working but I need to know if I can actually achieve this using the connection I have or am I missing something altogether that's stopping it from working?

Before I go forth and spend the next 2 days debugging something I'm unfamiliar with, I would like to know I'm at least on the right track...

解决方案

The code in your second snippet is correct, but should be applied to a new ADODB.Command object, not to the Connection object:

username = Trim(Request("username"))

'-----Added this-----

Dim cmdContent

Set cmdContent = Server.CreateObject("ADODB.Command")

' Use this line to associate the Command with your previously opened connection

Set cmdContent.ActiveConnection = connContent

'--------------------

cmdContent.Prepared = True

Const ad_nVarChar = 202

Const ad_ParamInput = 1

SQL = " SELECT * FROM users WHERE (username=?) ; "

Set newParameter = cmdContent.CreateParameter("@username", ad_nVarChar, ad_ParamInput, 20, username)

cmdContent.Parameters.Append newParameter

cmdContent.CommandText = SQL

Set rs = cmdContent.Execute

If NOT rs.EOF Then

' Do something...

End If

rs.Close

By the way, there was a typo with the spelling of adParamInput instead of ad_ParamInput (corrected in my example).