Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > asp insert

Reply
Thread Tools

asp insert

 
 
Eugene Anthony
Guest
Posts: n/a
 
      06-12-2006
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

*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
 
 
 
Jeff
Guest
Posts: n/a
 
      06-12-2006

"Eugene Anthony" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.


> %>
>
> Eugene Anthony
>
> *** Sent via Developersdex http://www.developersdex.com ***



 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      06-12-2006
Eugene Anthony wrote:
> 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/...e36562fee7804e

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

Access:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl


--
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 With Quote
 
Eugene Anthony
Guest
Posts: n/a
 
      06-13-2006
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

*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
Mike Brind
Guest
Posts: n/a
 
      06-13-2006

Eugene Anthony wrote:
> 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.

--
Mike Brind

 
Reply With Quote
 
Eugene Anthony
Guest
Posts: n/a
 
      06-13-2006
solve the vulnerable to hackers using sql injection.


Eugene Anthony

*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      06-13-2006
Eugene Anthony wrote:
> 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/adva..._injection.pdf
and
http://www.nextgenss.com/papers/more..._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
--
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 With Quote
 
Eugene Anthony
Guest
Posts: n/a
 
      06-13-2006
Pass by parameter, can u show me an example that suites my codes.

Eugene Anthony

*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      06-13-2006
Eugene Anthony wrote:
> 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

--
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 With Quote
 
Eugene Anthony
Guest
Posts: n/a
 
      06-13-2006
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%>">N ext</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

*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Multiple Records Using One Insert Statemen with MySQLdb module anton.ranieri.it@gmail.com Python 1 12-06-2008 12:47 PM
insert in std::map does not insert the object simon.elbaz@free.fr C++ 1 11-04-2007 11:42 PM
Insert & Insert/Repeat buttons in a detailsview eagle ASP .Net Datagrid Control 0 07-12-2007 12:47 PM
Detailsview loses insert mode contents when Insert fails... cannontrodder ASP .Net 1 07-25-2006 08:38 AM
DetailsView - Changing to Insert Mode (Not By Using the Insert Button In the DetailsView Control) Feege ASP .Net Web Controls 0 12-20-2005 12:28 AM



Advertisments