asp insert

[Eugene Anthony]

The code bellow functions well when I attemp to delete a record. But
when I attemp to insert a record I am getting the following error
-2147217900:Syntax error in INSERT INTO statement. How do I solve the
problem?.

<%
Sub GetData (sql)
on error resume next
set conn = server.CreateObject("ADODB.Connection")
conn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & Server.MapPath("/db/upload/stelladb.mdb") & ";"
conn.open
conn.execute(sql)
set conn = nothing
if Err.number <> 0 then
Response.Write(Err.number & ":" & Err.Description & "<br>")
end if
on Error goto 0
End Sub

If request.queryString("Action") = 1 then
GetData "DELETE * FROM Account WHERE ID=" &
Request.QueryString("ID")
End if

If request.queryString("Action") = 2 then
username = request.form("username")
password = request.form("password")
GetData "INSERT INTO Account(Username,Password) VALUES(" & "'" &
username & "','" & password & "')"
End if
%>

Eugene Anthony

 

[Jeff]

The code bellow functions well when I attemp to delete a record. But
when I attemp to insert a record I am getting the following error
-2147217900:Syntax error in INSERT INTO statement. How do I solve the
problem?.

<%
Sub GetData (sql)
on error resume next
set conn = server.CreateObject("ADODB.Connection")
conn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & Server.MapPath("/db/upload/stelladb.mdb") & ";"
conn.open
conn.execute(sql)
set conn = nothing
if Err.number <> 0 then
Response.Write(Err.number & ":" & Err.Description & "<br>")
end if
on Error goto 0
End Sub

If request.queryString("Action") = 1 then
GetData "DELETE * FROM Account WHERE ID=" &
Request.QueryString("ID")
End if

If request.queryString("Action") = 2 then
username = request.form("username")
password = request.form("password")
GetData "INSERT INTO Account(Username,Password) VALUES(" & "'" &
username & "','" & password & "')"
End if

VALUES(" & "'" &

username & "','" & password & "')"

what is the " & " for??
i would write it VALUES('" & username & "', '" & password & "')

but i am sure that others may have an even better way to do it.

 

[Bob Barrows [MVP]]

The code bellow functions well when I attemp to delete a record. But
when I attemp to insert a record I am getting the following error
-2147217900:Syntax error in INSERT INTO statement. How do I solve the
problem?.

The first step is to tell us the datatypes of the fields involved in the
query (Text? Number? Date/Time?).

The second step is to show us the sql statement ... and no, showing us
the vbscript code that is supposed to generate the sql statement is not
the same as showing us the resulting sql statement. You need to assign
the results of your concatenation to a variable and response.write the
variable:

sql="INSERT INTO Account(Username,Password) VALUES(" & _
"'" & username & "','" & password & "')"
'comment out the following two lines when finished debugging
'************************
response.write sql
response.end
'************************
GetData sql

I suspect your problem is due to the use of a reserved keyword,
"password" as the name of your field. You really should change that
name, but, if you can't, you will need to remember to bracket it
[password] when using it in your queries:

sql="INSERT INTO Account(Username,[Password]) VALUES(" & _
"'" & username & "','" & password & "')"


Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
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:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

 

[Eugene Anthony]

Will this solve the problem?

<%
function getUserInput(input,stringLength)
dim tempStr

tempStr = left(trim(input),stringLength)

' replace due to DB hack threats
tempStr = replace(tempStr,"--","")
tempStr = replace(tempStr,";","")
tempStr = replace(tempStr,"SCRIPT","s-c-r-i-p-t",1,-1,1)

getUserInput = tempStr
end function

function formatForDb(input)
dim tempStr
tempStr=input
' replace to avoid DB errors
tempStr = replace(tempStr,"'","''")
tempStr = replace(tempStr,"&"," and ")
formatForDb = tempStr
end function
%>

Eugene Anthony

 

[Mike Brind]

Will this solve the problem?

<%
function getUserInput(input,stringLength)
dim tempStr

tempStr = left(trim(input),stringLength)

' replace due to DB hack threats
tempStr = replace(tempStr,"--","")
tempStr = replace(tempStr,";","")
tempStr = replace(tempStr,"SCRIPT","s-c-r-i-p-t",1,-1,1)

getUserInput = tempStr
end function

function formatForDb(input)
dim tempStr
tempStr=input
' replace to avoid DB errors
tempStr = replace(tempStr,"'","''")
tempStr = replace(tempStr,"&"," and ")
formatForDb = tempStr
end function
%>

Solve which problem? The use of a reserved word as a field name? No.
The only thing you can do is as Bob suggested: bracket it [Password],
change it or use a saved parameter query. Access will automatically
bracket the field name for you if you use the last option.

I strongly recommend the saved parameter query option. Spend an hour
or so trying it out, and it will save you tons of time in the future.
You will wonder why you ever did database inserts, updates etc any
other way, and you won't need either of the functions above.

 

[Eugene Anthony]

solve the vulnerable to hackers using sql injection.


Eugene Anthony

 

[Bob Barrows [MVP]]

solve the vulnerable to hackers using sql injection.

Please quote some of the message to which you are replying.

For the answer to your question, see
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
and
http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf

particularly the part that talks about defenses.

Escaping quotes and replacing comment marks as you are doing only fixes
part of the problem. Experienced hackers can defeat this defense. The
best defense is to:
1. Use server-side validation to _reject_ input that is known to be
bad - don't try to massage it so it becomes "good" input. Raise an
error, but be careful not to give the hacker too much information. If
you detect a definite injection attack, just return a message such as
"Bad input" or something else that is unrevealing. For other validation
failures that may be honest mistakes, return a more helpful error
message. But ... never reveal information about your database or website
structure in these error messages. One of the ways hackers work is to
cause errors that reveal information. You must trap all errors, logging
them and returning unrevealing messages.
2. For injection attempts that you miss in step 1 (and you WILL miss
some), use parameters instead of concatenation. Direct SQL Injection is
impossible when data is passed by parameter (although secondary
injection is still possible if you get careless)

Bob Barrows

 

[Eugene Anthony]

Pass by parameter, can u show me an example that suites my codes.

Eugene Anthony

 

[Bob Barrows [MVP]]

Pass by parameter, can u show me an example that suites my codes.

<sigh>
Why aren't you quoting?
I guess you are talking about this code:

%>
Sub GetData (sql)
on error resume next
set conn = server.CreateObject("ADODB.Connection")
conn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & Server.MapPath("/db/upload/stelladb.mdb") & ";"
conn.open
conn.execute(sql)
set conn = nothing
if Err.number <> 0 then
Response.Write(Err.number & ":" & Err.Description & "<br>")
end if
on Error goto 0
End Sub

If request.queryString("Action") = 1 then
GetData "DELETE * FROM Account WHERE ID=" &
Request.QueryString("ID")
End if

If request.queryString("Action") = 2 then
username = request.form("username")
password = request.form("password")
GetData "INSERT INTO Account(Username,Password) VALUES(" & "'" &
username & "','" & password & "')"
End if
%>

OK, first off, why are you calling this sub "GetData"? It does not
return any data. Let's start by renaming it "RunQueryString"

Sub RunQueryString(pSQL, parms)
on error resume next
dim cn,cmd
set conn = server.CreateObject("ADODB.Connection")
conn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("/db/upload/stelladb.mdb")
set cmd=createobject("adodb.command")
With cmd
.CommandText=pSQL
.commandType= 1 'adCmdText
set .ActiveConnection=conn
err.clear
.Execute ,parms,128
if err<>0 then
Response.Write Err.number & ":" & _
Err.Description & "<br>"
end if
on Error goto 0
End With
End Sub
dim sql, arParms, id
If request.queryString("Action") = 1 then
id=Request.QueryString("ID")
'First validate id
'Then, if valid, do this
arParms=Array(clng(id))
sql="DELETE * FROM Account WHERE ID=?"
RunQueryString sql, arParms
End if

If request.queryString("Action") = 2 then
username = request.form("username")
password = request.form("password")
'First validate these inputs, then
arParms=Array(username,password)
sql="INSERT INTO Account(Username,[Password])" & _
"VALUES(?,?)"
RunQueryString sql, arParms
End if

 

[Eugene Anthony]

The code you have given works well, however after inserting the record
and deleting the record no changes are shown to the records displayed
using this code:

<html>
<head>
<title></title>
</head>
<body>
<%
Dim conn, rs
Dim currentPage, rowCount, i
currentPage = Trim(Request("CurrentPage"))
if currentPage = "" then currentPage = 1 end if
set conn = server.CreateObject("ADODB.Connection")
conn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& Server.MapPath("/db/upload/stelladb.mdb") & ";"
conn.open
set rs = server.CreateObject("ADODB.Recordset")
rs.CursorType = 3
rs.PageSize = 1
rs.Open "SELECT Username, Password FROM Account",conn
rs.AbsolutePage = cInt(currentPage)
rowCount = 0

while not rs.EOF and rowCount < rs.PageSize
response.write rs("Username") & "<BR>"
rowCount = rowCount + 1
rs.movenext
wend
%>

<% If CInt(currentPage) > 1 Then %>
<A HREF="paging.asp?currentPage=<%=currentPage-1%>">Prior</A>
<% End If %>

<% If CInt(currentPage) < CInt(rs.PageCount) Then %>
<A HREF="paging.asp?currentPage=<%=currentPage+1%>">Next</A>
<% End If %>

</body>
</html>

It seems that I have to refresh the webpage by pressing the refresh
button on my internet explorer to see the results.

Eugene Anthony

 

[Eugene Anthony]

now it works fine, I simple had to close the connection as shown bellow:

<%
Sub RunQueryString (pSQL,parms)

conn.close
Set conn = nothing

End Sub

Eugene Anthony

 

[Mike Brind]

<sigh>
Why aren't you quoting?

I think it's a limitation of using developersdex for posting to the
group.

Eugene, have a look at
http://groups.google.com/group/microsoft.public.inetserver.asp.general,
and see if you can use that for posting?