Best way to 'secure' SQL entries (stray quotes and such)

[Darrel]

I have a lot of fields where people can enter in data. If they enter in
anything with an apostrophe, this messes up the SQL query and errors out.

I know I shouldn't allow quotes and apostrophes anyway for security reasons.
So, as such, what's the recommended method for allowing entry of these
chracters. Should I just make a shared class that simply search and replaces
on input to double-up these characters to escape them and then do the
reverse when grabbing the data back out? Or is there a more accepted method
of accomplishing this?

-Darrel

 

[Karl Seguin]

Pass these values as parameters:

dim command as new SqlCommand("SELECT * FROM Products Where Name = @Name")
command.Parameters.Add("@Name", SqlDbType.VarChar, 256).Value = Products

if you are using dynamic SQL in your sproc you can do the same thing with
sp_ExecuteSQL

Karl

 

[Darrel]

Pass these values as parameters:

dim command as new SqlCommand("SELECT * FROM Products Where Name = @Name")
command.Parameters.Add("@Name", SqlDbType.VarChar, 256).Value = Products

if you are using dynamic SQL in your sproc you can do the same thing with
sp_ExecuteSQL

Thanks, Karl...but I'm not sure what the above is. Is that a setting I pass
with the INSERT command? What's a Sproc? (Off to google these thing in the
interim. ;o)

-Darrel

 

[Karl Seguin]

Perhaps you could show me what your SQL command looks like now. sproc are
stored procedures...sorta functions which reside on the database instead of
inside your code.

For an insert statement it isn't too different:
dim command as new SqlCOmmand("INSERT INTO MyTable (Column1, Column2,
Column3) VALUES (@value1, @value2, @value3)")
command.parameters.add("@value1", SqlDbType.VarChar, 1024).Value = someValue
command.parameters.add("@value2", SqlDbType.Char, 2).Value = someOtherValue
command.parameters.add("@value3", SqlDbType.Int).Value = oneLastValue

someValue, someOtherValue and oneLastValue are values you are passing into
your insert statement...instead of doing:
dim command as new SqlCOmmand("INSERT INTO MyTable (Column1, Column2,
Column3) VALUES ('" + someValue + "', '" + someOtherValue + "', '" +
oneLastValue + "')")
in which case you need to worry about single quotes ("secure" it), you can
do it the above way which will make it so you don't have to worry about such
things...

Karl

 

[Andy G]

Darrel here is what I've done...this was in a MS how to document somewhere.
I just pass whatever the user user for a login to this funtion and replaces
any possible 'bad' characters.
Public Function SafeSqlLikeClauseLiteral(ByVal inputSQL As String) As String

' Make the following replacements:

' ' becomes ''

' [ becomes [[]

' % becomes [%]

' _ becomes [_]

Dim s As String = inputSQL

s = inputSQL.Replace("'", "''")

s = s.Replace("[", "[[]")

s = s.Replace("%", "[%]")

s = s.Replace("_", "[_]")

Return (s)

End Function