SQL String query

Question

hey all

i have this insert query im tryin to do but it isn't working. no matter how many variations, and variations of variations i try, it always has a problem with my code.

Set rstSimple = cnnSimple.Execute("insert into SALT (Email, Username, FirstName, LastName, ActivationCode, TransactionID, ClientID) VALUES (<%=Request.QueryString(payer_email)%>, <%=Request.QueryString(payer_email)%>, <%=Request.QueryString(first_name)%>, <%=Request.QueryString(last_name)%>, <%=Request.QueryString(hash)%>, <%=Request.QueryString(txn_id)%>, <%=Request.QueryString(client_id)%>)")

I don't understand what its problem is: it keeps saying:

Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/thanks.asp, line 62

Set rstSimple = cnnSimple.Execute("insert into SALT (Email, Username, FirstName, LastName, ActivationCode, TransactionID, ClientID) VALUES (<%=Request.QueryString(payer_email)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^

Note: VBScript & ASP.

Can somebody please help me figure this out? Thank you.

Answer 1

I guess the parameters you're passing to Request.QueryString() should be enclosed in quotes:

Request.QueryString("payer_email")

---

UPDATE: Yes, and as @PHPology suggested, you should remove the <% %> and simply use string concatenation with &.

Try it like this:

Set rstSimple = cnnSimple.Execute("insert into SALT (Email, Username, FirstName, LastName, ActivationCode, TransactionID, ClientID) VALUES ('" & Request.QueryString("payer_email") & "', '" & Request.QueryString("payer_email") & "', '" & Request.QueryString("first_name") & "', '" & Request.QueryString("last_name") & "', '" & Request.QueryString("hash") & "', '" & Request.QueryString("txn_id") & "', '" & Request.QueryString("client_id") & "')")

---

Although not related to the problem, your query also appears to be vulnerable to SQL Injection.

Answer 2

Instead of doing...

"foo <%=bar%> foo"

...try this instead:

"foo" & bar & "foo"

Answer 3

remove your <% %> as you are already in ASP script mode. it should be something like this for your values

'"& Request.QueryString("payer_email")&"', '"& Request.QueryString("first_name")&"', etc, etc

Answer 4

You need to place quotes around your <%=Request.QueryString(xyz)%> expressions, and your parameter names, and as you're in script, you don't do <%= %>. e.g. as below:

insert into ... VALUES ('" &  Request.QueryString("payer_email") & "', ...)

EDIT:

Here's the full statement:

Set rstSimple = cnnSimple.Execute("insert into SALT (Email, Username, FirstName, LastName, ActivationCode, TransactionID, ClientID) VALUES ('" & Request.QueryString("payer_email") & "','" & Request.QueryString("payer_email") & "','" & Request.QueryString("first_name") & "','" & Request.QueryString("last_name") & "','" & Request.QueryString("hash") & "','" & Request.QueryString("txn_id") & "','" & Request.QueryString("client_id") & "')")

This comes with the usual BEWARE caveats about SQL injection.

Answer 5

This isn't answering your question, but note that for string fields you will also need to quote the strings, i.e.

VALUES ('<%=Request.QueryString(payer_email)%>', 

Your code is also very prone to SQL injection attacks - this will be an issue if your system is internet facing.

Answer 6

There are several problems with the code:

• You are using server tags (<%= %>) in the server code.
• You don't have quotation marks around the query string key names.
• You don't have apostrophes around the string values in the query.
• The string values are not encoded, so the query is wide open for SQL injection attacks.

You need a function to encode the strings, this works for MS Access and MS SQL Server:

Function SqlEncode(str)
  SqlEncode = Replace(str, "'", "''")
End Function

Then you use that on the string values in the query:

Set rstSimple = cnnSimple.Execute( _
  "insert into SALT " & _
  "(Email, Username, FirstName, LastName, ActivationCode, TransactionID, ClientID) VALUES (" & _
  "'" & SqlEncode(Request.QueryString("payer_email")) & "', " & _
  "'" & SqlEncode(Request.QueryString("payer_email")) & "', " & _
  "'" & SqlEncode(Request.QueryString("first_name")) & "', " & _
  "'" & SqlEncode(Request.QueryString("last_name")) & "', " & _
  "'" & SqlEncode(Request.QueryString("hash")) & "', " & _
  "'" & SqlEncode(Request.QueryString("txn_id")) & "', " & _
  "'" & SqlEncode(Request.QueryString("client_id")) & "'" & _
  ")")