Compare Form Data against varchar DB record.

aspless · June 26th, 2007, 10:07 AM

After being inspired by the above code i have attempted to write some compare code myself ..

I have a table called asumm_spelling which has two columns (spelling,cspelling).
I am trying to compare a form input to both columns.

From the above examples i created the below code.

set rs=Server.CreateObject("ADODB.Recordset")
         set conn = server.CreateObject ("ADODB.Connection")
         Conn.connectionstring = strConn
         Conn.Open
         rs = "SELECT spelling,cspelling FROM asumm_spelling WHERE " & _
            "spelling='" & question2 & "' " & _
            "or cspelling = '" & question2 & "'"
         Set rs = conn.Execute(rs)
         If Not rs.EOF Then
          Do While Not rs.EOF
           If rs("spelling") = question2 or rs("cspelling") = question2 Then id41 = "1" else id41 = "0"
         ' Close recordset here and redirect
         rs.MoveNext()
          Loop
         End If
         rs.close
         conn.close
         set rs=nothing
         set conn=nothing

The compare works for the first column but no the second.
Not being the brightest spark in the fire has anyone got any ideas.

Cheers

aspless

aspless · June 26th, 2007, 10:38 AM

Well completely out of character i have fixed my own problem.

As usual it was me trying to over complicate the code.

After re-reading the above code i have amended to the below.

         set rs=Server.CreateObject("ADODB.Recordset")
         set conn = server.CreateObject ("ADODB.Connection")
         Conn.connectionstring = strConn
         Conn.Open
         rs = "SELECT spelling,cspelling FROM asumm_spelling WHERE " & _
            "spelling='" & question2 & "' " & _
            "or cspelling = '" & question2 & "'"
         Set rs = conn.Execute(rs)
         If Not rs.EOF Then id41 = "1" else id41 = "0"
         rs.close
         conn.close
         set rs=nothing
         set conn=nothing

It seems to work a treat!

How ever if there is a cleaner way please educate.

aspless

Imar · June 26th, 2007, 02:02 PM

Hi there,

Well, this is pretty much as optimised as it can get. Few things though:

1. You don't need to actually select the data to see if it's there. So this:

Code:

"SELECT 1 FROM asumm_spelling WHERE " & _
    "spelling='" & question2 & "' " & _
    "or cspelling = '" & question2 & "'"

would e slightly faster because it doesn't retrieve the actual data. If the database is on a different server, you may save yourself from some network overhead. By selecting just the number 1 (or something else) you transfer only that, and not the value of spelling and cspelling.

2. Alternatively, you can use COUNT:

Code:

"SELECT COUNT(*) FROM asumm_spelling WHERE " & _
    "spelling='" & question2 & "' " & _
    "or cspelling = '" & question2 & "'"

Count returns the number of records so you could do this:

Code:

Set rs = conn.Execute(rs)

Code:

    id41 = CInt(rs(0))
    rs.close

Depending on how many rows your query returns, id41 could be 0, 1 or more.

3. IMO, it is better to have a separate variable for the SQL statement. ASP is quite forgiving when it comes to type swapping, but it looks a bit weird to store the SQL statement (a string) in rs which becomes a recordset after the Execute method. This would be a little cleaner:

Code:

Dim sql
sql = "SELECT spelling,cspelling FROM asumm_spelling WHERE " & _    
    "spelling='" & question2 & "' " & _
    "or cspelling = '" & question2 & "'"
    Set rs = conn.Execute(sql)
    If Not rs.EOF Then id41 = "1" else id41 = "0"
    rs.close

Same effect, but easier to understand for others.

4. Depending on the source of question2, your code may be open to SQL injection.

Hope this helps,

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

aspless · July 2nd, 2007, 12:58 PM

Thanks Imar,

I have started to split my SQL statements separately which does read better.

Also thanks for clearing a confusion i had with retrieving data as i was under the impression using * took longer than specifying the data.

Re your SQL injection the input is a hidden form entry from one of the previous questions re-named from a request.form.

Cheers

aspless

Imar · July 2nd, 2007, 01:08 PM

Using * does take longer or more resources. If you have 20 columns in a table, and you only need two, you should specify them explicitly to avoid retrieving the other 18 from the database.

My example showed the use of COUNT(*) which doesn't retrieve the actual data, but only counts the number of records. That's a big difference...

Regarding the hidden field: don't trust user input, even when it comes from a value you set yourself. It's easy to modify a hidden field in a self constructed form and send illegal data to the server. You'd be surprised at how smart some of the SQL hack attempts are, and the number of attempts that are fired at your server. I think I have at least 50 to 100 attempts a day on my own web server alone....

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

aspless · July 2nd, 2007, 01:35 PM

Again Thanks Imar,

I now understand your point on using COUNT(*).

Much simpler and another way of providing the same report outcome.

Also a long read of some SQL injection articles is good advice as i am still very new to both ASP and SQL as name would indicate!

Cheers

aspless

Imar · July 2nd, 2007, 01:40 PM

Hi there,

You're welcome.

For a long (25 pages) and scary read on SQL injection, check out this PDF:

http://www.spidynamics.com/support/w...LInjection.pdf

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.