|
|
Posted by Dan Guzman on 03/10/07 17:05
> sql=sql & "PhoneNumber='" & PhoneNumber & "',"
It looks like the syntax error is due to the extraneous comma after the last
column.
I strongly suggest you google 'SQL injection'. Your current code will allow
a hacker can execute any arbitrary SQL statement. The best protection
against injection is to use parameterized SQL statements, stored procedures
and validate user input. Never build a SQL Statement string by
concatenating user input values. The example below uses a parameterized
UPDATE statement via OLEDB:
Const adParamInput = 1
Const adInteger = 3
Const adVarChar = 200
Set Conn = CreateObject("ADODB.Connection")
Set Command = CreateObject("ADODB.Command")
Conn.Open _
"Provider=SQLOLEDB;" & _
"Data Source=(local);" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=FriendsContactInfo;" & _
"App=AspRunner Professional Application"
Command.ActiveConnection = Conn
Command.CommandText = _
" UPDATE dbo.FPFriends" & _
" SET" & _
" Name=?," & _
" StreetAddress=?," & _
" Ciy=?," & _
" State=?," & _
" Zip=?," & _
" PhoneNumber=?" & _
" WHERE Id=?"
Set parameter = Command.CreateParameter( _
"Name", _
adVarChar, _
adParamInput, _
30)
parameter.Value = Name
Command.Parameters.Append parameter
Set parameter = Command.CreateParameter( _
"StreetAddress", _
adVarChar, _
adParamInput, _
30)
parameter.Value = StreetAddress
Command.Parameters.Append parameter
Set parameter = Command.CreateParameter( _
"City", _
adVarChar, _
adParamInput, _
30)
parameter.Value = City
Command.Parameters.Append parameter
Set parameter = Command.CreateParameter( _
"State", _
adVarChar, _
adParamInput, _
2)
parameter.Value = State
Command.Parameters.Append parameter
Set parameter = Command.CreateParameter( _
"Zip", _
adVarChar, _
adParamInput, _
5)
parameter.Value = Zip
Command.Parameters.Append parameter
Set parameter = Command.CreateParameter( _
"PhoneNumber", _
adVarChar, _
adParamInput, _
15)
parameter.Value = PhoneNumber
Command.Parameters.Append parameter
Set parameter = Command.CreateParameter( _
"Id", _
adInteger, _
adParamInput)
parameter.Value = Id
Command.Parameters.Append parameter
Command.Execute
Conn.Close
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DaveF" <jeacdf@excite.com> wrote in message
news:1173540573.073247.128620@t69g2000cwt.googlegroups.com...
> Any Ideas as to this error message. I am trying to learn using ms sql
> server 7.0
>
> Below is the code I am using for an update to a MS Sql Database.
>
> <%@ Language=VBScript %>
> <% Option Explicit %>
>
> <html>
> <head>
> <title>Sample Script 2 - Part 3 </title>
> <!-- copyright MDFernandez --->
> <link rel="stylesheet" type="text/css" href="../part3sol/style.css">
> </head>
> <body bgcolor="#FFFFFF">
> <!--#include virtual="/adovbs.inc"-->
>
> <center>
> <%
>
> Dim oRS
> Dim Conn
>
> Dim Id
> Dim Name
> Dim StreetAddress
> Dim City
> Dim State
> Dim Zip
> Dim PhoneNumber
> dim sql
>
> Id = request.form("Id")
> Name = request.form("Name")
> StreetAddress = request.form("StreetAddress")
> City = request.form("City")
> State = request.form("State")
> Zip = request.form("Zip")
> PhoneNumber = request.form("PhoneNumber")
>
>
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.open =("DRIVER=SQL Server;SERVER=(local);UID=;APP=AspRunner
> Professional
> Application;WSID=COMPAQAM;DATABASE=FriendsContactInfo;Trusted_Connection=Yes")
> 'Conn.Open
>
>
> sql="update FPFriends"
> sql=sql & " set Name='" & Name & "',"
> sql=sql & "StreetAddress='" & StreetAddress & "',"
> sql=sql & "Ciy='" & City & "',"
> sql=sql & "State='" & State & "',"
> sql=sql & "Zip='" & Zip & "',"
> sql=sql & "PhoneNumber='" & PhoneNumber & "',"
> sql=sql & " WHERE Id=" & Id
>
>
>
> set oRS=Conn.Execute (sql)
> response.write "<font face='arial' size=4>"
> response.write "<br><br>The record has been updated."
> response.write "</b></font>"
> ' close the connection to the database
> Conn.Close
> %>
> <!-- don't include in sample code display --->
> <form>
> <input type="button" value=" Close This Window "
> onClick="window.location='aboutus.htm'"><br>
> <button onClick="window.location='menu1_1.asp'">Update another
> record</button>
>
> </form>
>
> </center>
> </body>
> </html>
>
[Back to original message]
|