SQL String query

Question

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.

Neil Moss · Answer 1 · 2010-07-30T08:55:08.477

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.

score 0 · Accepted Answer · edited May 23 '17 at 10:27

0

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.

edited May 23 '17 at 10:27

Community

1
1

answered Jul 30 '10 at 08:15

Daniel Vassallo

337,827
72
505
443

That's what I assumed at first. But not, it just gives me a different error. If I remember correctly, it was: "Expected end of line character". But like I said, it has a problem with everything. – Jul 30 '10 at 08:17
Thank you. Your update worked, mostly. Now I just keep getting this error: Invalid column name 'Email'. Which makes me think that there is no Email column in the table, but even after I add one, it still bring up that same error. – Jul 30 '10 at 08:46

score 0 · Answer 3 · answered Jul 30 '10 at 08:17

0

Instead of doing...

"foo <%=bar%> foo"

...try this instead:

"foo" & bar & "foo"

answered Jul 30 '10 at 08:17

Amber

507,862
82
626
550

score 0 · Answer 4 · answered Jul 30 '10 at 08:17

0

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

answered Jul 30 '10 at 08:17

PHPology

1,017
6
12

Thanks. But now it says: Microsoft VBScript compilation error '800a03ee' Expected ')' /thanks.asp, line 62 – Jul 30 '10 at 08:34

score 0 · Answer 5 · answered Jul 30 '10 at 08:19

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.

score 0 · Answer 6 · answered Jul 30 '10 at 08:55

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

SQL String query

6 Answers6