473,226 Members | 1,496 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

asp insert

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 ***
Jun 12 '06 #1
11 1958

"Eugene Anthony" <so***********@yahoo.com> wrote in message
news:uH**************@TK2MSFTNGP04.phx.gbl...
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 ***

Jun 12 '06 #2
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.
Jun 12 '06 #3
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 ***
Jun 13 '06 #4

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

Jun 13 '06 #5
solve the vulnerable to hackers using sql injection.
Eugene Anthony

*** Sent via Developersdex http://www.developersdex.com ***
Jun 13 '06 #6
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.
Jun 13 '06 #7
Pass by parameter, can u show me an example that suites my codes.

Eugene Anthony

*** Sent via Developersdex http://www.developersdex.com ***
Jun 13 '06 #8
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.
Jun 13 '06 #9
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 ***
Jun 13 '06 #10
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

*** Sent via Developersdex http://www.developersdex.com ***
Jun 14 '06 #11

Bob Barrows [MVP] wrote:
Eugene Anthony wrote:
Pass by parameter, can u show me an example that suites my codes.

<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/micro...er.asp.general,
and see if you can use that for posting?

--
Mike Brind

Jun 14 '06 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Howard Hinnant | last post by:
I recently asked for a survey of multimap insert with hint behavior, in support of a paper I'm writing concerning lwg issue 233. My sincere thanks to Beman Dawes, Raoul Gough, Russell Hind, Bronek...
6
by: Mark P | last post by:
Some time ago I posted here about inserting into a set with a hint: ...
14
by: serge | last post by:
I have a scenario where two tables are in a One-to-Many relationship and I need to move the data from the Many table to the One table so that it becomes a One-to-One relationship. I need to...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
8
by: Carl | last post by:
Hi, I hope someone can share some of their professional advice and help me out with my embarissing problem concerning an Access INSERT query. I have never attempted to create a table with...
4
by: Chris Kratz | last post by:
Hello all, We have run into what appears to be a problem with rules and subselects in postgres 7.4.1. We have boiled it down to the following test case. If anyone has any thoughts as to why...
2
by: Geoffrey KRETZ | last post by:
Hello, I'm wondering if the following behaviour is the correct one for PostGreSQL (7.4 on UNIX). I've a table temp_tab with 5 fields (f1,f2,f3,...),and I'm a launching the following request :...
3
by: MP | last post by:
Hi Posted this several hours ago to another ng but it never showed up thought i'd try here. using vb6, ado, .mdb, jet4.0, no access given table tblJob with field JobNumber text(10) 'The...
6
by: lenygold via DBMonster.com | last post by:
Hi everybody: What is the best way to I have 10 tables with similar INSERT requiremnts. INSERT INTO ACSB.VAATAFAE WITH AA(AA_TIN, AA_FILE_SOURCE_CD, .AA_TIN_TYP) AS ( SELECT AA_TIN,...
1
by: EJO | last post by:
with sql 2000 enterprise Trying to build a stored procedure that will take the rows of a parent table, insert them into another table as well as the rows from a child table to insert into...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.