Thursday, March 29, 2012

Correct syntax for an update stored procedure

This is probably a very simple question but i would appreciate some helpwith the correct syntax for andupdate stored procedure

I have created user form that allows the user toupdate thenameandaddress fields in adatatable called customers based on the input valuecustomer ID = ( datatable/Customers)customerID
I have got this far and then got lost:
Create SP_UpdateCustomer
(@.customerID, @.name, @.address)
As
Update customers ( name, address)
Where customerID = @.customerID

GO
Could anyone tell me what the correct sntax should be.
many thanks
MartinHi Martin,
You'll need to specify the data types in the create clause, and add a set clause to change the fields you want to update:
create proc sp_updatecustomer (@.customerid varchar(50), @.name varchar(50), @.address varchar(50))
as
update customers
setname=@.name,address=@.address
where customerID= @.customerID
Note - this might be a bit dangerous from a security standpoint, and you might also want to introduce some validation on the Customer ID field, to avoid anyone maliciously changing all the records by entering a customer ID of "a or 1=1"
|||

rJonas
Many thanks for your reply
I note the securtiy points you made
Thank you
martin

|||

rjonas wrote:


Note - this might be a bit dangerous from asecurity standpoint, and you might also want to introduce somevalidation on the Customer ID field, to avoid anyone maliciouslychanging all the records by entering a customer ID of "a or 1=1"


?? That is not physcially possible with the stored procedure the posteris using. The stored procedure is corerctly parameterized and thedanger you pointed out does not exist here.
Here are some articles on SQL injection and parameterized queries:
Please, please, please, learn about injection attacks!
How To: Protect From SQL Injection in ASP.NET
Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2
Using Parameterized Queries in ASP.Net

No comments:

Post a Comment