Syntax error reported near the "Select" keyword

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GailS - 17 Jun 2008 16:34 GMT

Hello,

The code below results in a reported syntax error near the keyword "Select".
I copied most of this from a perfectly functioning application I worked on a
couple of years ago, that still works, just changing the references. I can't
see what it is referring to. The line number listed in the error message
references the open statement directly.

    Dim rsCodes, strSelect, strEMsg, strLevel
    Set rsCodes = Server.CreateObject("ADODB.Recordset")
    strSelect = "SELECT * from MemberCodes WHERE code = '" & strCode & "'"
    rsCodes.open strSelect, objConn

The objConn is set up in a connex.asp included file at the beginning of the
page:

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=SQLOLEDB; DataSource=****; " & _
"Database=*****; User ID=***; Password=*****"

Perhaps more experienced eyes can spot my syntax problem. Thanks in advance!

Signature

Gail S
Gent-l-kleen Products, Inc.
York, PA

Reply to this Message

Bob Barrows [MVP] - 17 Jun 2008 16:50 GMT

> Hello,
>
[quoted text clipped - 4 lines]
> line number listed in the error message references the open statement
> directly.

Yes, it would. The error is coming from the database engine, and the
first chance the database engine has to parse the query is when the Open
statement is executed.

> Dim rsCodes, strSelect, strEMsg, strLevel
> Set rsCodes = Server.CreateObject("ADODB.Recordset")
> strSelect = "SELECT * from MemberCodes WHERE code = '" & strCode & "'"
> rsCodes.open strSelect, objConn

You cannot debug a syntax problem in a sql statement without knowing
what that statement is. The only way to find out is to write it to
Response:

Response.Write strSelect

Run the page and look at the statement in the browser window. Does it
look like it should work? Can you copy-paste it to the sql execution
tool of whatever database you are using (pre-2005 SQL Server: Query
Analyzer; SQL 2005: SSMS ) and execute it with no modifications? Still
can't figure it out? Show it to us. :-)

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection (look at some recent posts in the .general group for further
info):
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers (tokens):
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36
562fee7804e

Personally, I prefer using stored procedures. See
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9
d4409dc1701?hl=en

Signature

Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Reply to this Message

GailS - 17 Jun 2008 19:07 GMT

Bob,

Again, thanks for the assistance. I plugged in the Response.Write strSelect,
and not only did the string write, but the page functioned properly. I don't
think I did anything differently! Very strange. But I will review all the
information you shared to see if I can tighten up any potential security
problems.

Gail S
Gent-l-kleen Products, Inc.
York, PA

> > Hello,
> >
[quoted text clipped - 39 lines]
> Personally, I prefer using stored procedures. See
> http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9
d4409dc1701?hl=en

Reply to this Message

Webmaster Forum / ASP / Database Access / June 2008

Syntax error reported near the "Select" keyword