Validateing values in Stored Procedures

[hhrafn]

Hi, I have, for example, a Stored Procedure wich validates users into my web. I'd like to validate the users's input in order to enhance security. What would be the best way to validate the input inside the Stored Procedure? for example I'd like to make sure that numbers are actually numbers and username like "joe" isn't something like "'; DROP table;--" thanks, H

[Colin Angus Mackay]

hhrafn wrote: for example I'd like to make sure that numbers are actually numbers and username like "joe" isn't something like "'; DROP table;--" Well, if you are using stored procedure this shouldn't be too much of a problem. You are passing the parameters to the stored procedures correctly, aren't you? I mean your code is something like this:

SqlCommand cmd = new SqlCommand("MyStoredProcName");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@userNameParam", "joe");

If you add parameters in this way you are protected from a first order attack. If your command string is something like

EXEC MyStoredProcName @userNameParam = 'joe'

then you should replace it with code like the first example above. In your stored procedure you can then have code like this

IF EXISTS (SELECT * FROM UsersTable WHERE UserName = @userNameParam)
BEGIN
-- Do stuff based on the user existing
END
ELSE
BEGIN
-- Do stuff based on this being an incorrect user
END

For more information see SQL Injection Attack and Tips on How to Prevent Them[^] Does this help?

---

My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More