SQL INSERT INTO statement

I keep getting "Syntax error in INSERT INTO statement" when i try to
execute the following code:


<%

p_username = Request("p_username")
p_password1 = Request("p_password1")
p_password2 = Request("p_password2")
p_firstname = Request("p_firstname")
p_lastname = Request("p_lastname")
p_email = Request("p_email")

%>

<%

if p_username = "" then

Response.Redirect "/homepage/register.asp?retry=username"

else

if p_password1= "" then

Response.Redirect "/homepage/register.asp?retry=password"

end if

if p_password2= "" then

Response.Redirect "/homepage/register.asp?retry=password"


end if
end if
%>

<% if p_password1 <> p_password2 then

Response.Redirect "/homepage/register.asp?retry=passmismatch"

end if
%>

<%
if p_password1 = p_password2 then

set DataBase = Server.CreateObject ("ADODB.Connection")
Const cDSN="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
DataBase.Open cDSN & Server.MapPath (cMDB)
const cMDB = "spencer.mdb"

sqlText = "select * from members where username = '"& p_username &"'"
set userSet = DataBase.Execute (sqlText)

if userSet.EOF then

theSQ = "insert into members"
theSQ = theSQ & "(username, password, first_name, last_name, email)"
theSQ = theSQ & "values ('"&p_username&"', '"&p_password1&"',
'"&p_firstname&"', '"&p_lastname&"', '"&p_email&"')"

end if

DataBase.Execute (theSQ)

DataBase.Close
set DataBase = Nothing



Response.Write "

User Registration

Thank you for your registration, if you wish you
may now ."

else

Response.Redirect "/homepage/register.asp?retry=usernameunavail"

end if

%>

The error is pointing to this particular line

DataBase.Execute (theSQ)

Does anybody see a problem with the above code or know a solution?

Many Thanks

Spencer

*** Sent via Developersdex http://www.developersdex.com ***

Re: SQL INSERT INTO statement

Spencer Hurst wrote:
> I keep getting "Syntax error in INSERT INTO statement" when i try to
> execute the following code:
>
>
> <%
>
> p_username = Request("p_username")
> p_password1 = Request("p_password1")
> p_password2 = Request("p_password2")
> p_firstname = Request("p_firstname")
> p_lastname = Request("p_lastname")
> p_email = Request("p_email")
>
> %>
>

> <%
> if p_password1 = p_password2 then
>
> set DataBase = Server.CreateObject ("ADODB.Connection")
> Const cDSN="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
> DataBase.Open cDSN & Server.MapPath (cMDB)
> const cMDB = "spencer.mdb"

Is this really your code? Why redefine cMDB after opening the connection?
Anyways, nothing to do with your problem, but you should not be opening your
connection until you are ready to use it. You still have not created your
sql string at this point. Wait until you have a string that is ready to
execute before opening your connection.

>
> sqlText = "select * from members where username = '"& p_username
> &"'"

Avoid selstar in production code. (http://www.aspfaq.com/show.asp?id=2096)

Now open the connection

> set userSet = DataBase.Execute (sqlText)
> if userSet.EOF then
>

Close and destroy your recordset at this point. Always release expensive
resources as soon as you are finished with them. Actually, I would do
something like this:
bEOF = userSet.EOF
userSet.close: set userSet = nothing
if bEOF then

> theSQ = "insert into members"
> theSQ = theSQ & "(username, password, first_name, last_name,
> email)" theSQ = theSQ & "values ('"&p_username&"',
> '"&p_password1&"', '"&p_firstname&"', '"&p_lastname&"',
> '"&p_email&"')"
>
> end if
>

You cannot debug a sql statement without knowing what it is. Do this:

response.write thesq
response.end

Look at it in the browser.
I'm not positive, but I think you will find that you need a space before the
word "values"
If you have created the statement correctly, you should be able to open your
database in Access, switch to the Queries tab, create a new query in Design
View, switch to SQL view, paste in the text copied from the browser window
and run it as-is. Testing in this manner will sometimes result in more
informative error messages than the ones reported by ADO.

Bob Barrows

*By using dynamic sql, you are leaving your site and database vulnerable to
hackers using sql injection. You are compounding your vulnerability by
failing to adequately validate the inputs. You can avoid the extra
validation and eliminate the danger of sql injection by ceasing your use of
dynamic sql, using parameters to pass data instead, either via my preferred
technique of using saved parameter queries:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl

http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl


or via the use of odbc parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

SQL Injection links:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection. pdf
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf


--
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.

Re: SQL INSERT INTO statement

Wow, that was really helpful Bob, as you can probably see from my code I
am very new to this ASP stuff and tend to go around things the long way!
I did exactly as you said by running the query in access after i output
the statemnt in my browser and it executed perfectly. However, it is
still saying there is a problem with my INSERT INTO statement. I
actually can't see a problem with that particular statement, have you
any other suggestions?

Once Again,

Many Thanks

Spencer

*** Sent via Developersdex http://www.developersdex.com ***

Re: SQL INSERT INTO statement

"Spencer Hurst" wrote in message
news:uKZ8Cp4vFHA.908@tk2msftngp13.phx.gbl...
> Wow, that was really helpful Bob, as you can probably see from my code I
> am very new to this ASP stuff and tend to go around things the long way!
> I did exactly as you said by running the query in access after i output
> the statemnt in my browser and it executed perfectly. However, it is
> still saying there is a problem with my INSERT INTO statement. I
> actually can't see a problem with that particular statement, have you
> any other suggestions?

"Password" is a reserved word for the Jet database engine. You'll need to
wrap the password column name in square brackets. Here's an article with a
list of other reserved words:
http://aspfaq.com/show.asp?id=2080

Re: SQL INSERT INTO statement

Spencer Hurst wrote:
> Wow, that was really helpful Bob, as you can probably see from my
> code I am very new to this ASP stuff and tend to go around things the
> long way! I did exactly as you said by running the query in access
> after i output the statemnt in my browser and it executed perfectly.
> However, it is still saying there is a problem with my INSERT INTO
> statement. I actually can't see a problem with that particular
> statement, have you any other suggestions?
>
It looks like Chris has spotted your problem. In the future, it will help us
out if you post the result of that response.write statement.

Bob Barrows
--
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.