473,320 Members | 1,841 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,320 software developers and data experts.

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

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
Nov 18 '05 #1
4 1157
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

--
MY ASP.Net tutorials
http://www.openmymind.net/
"Darrel" <no*****@nospam.com> wrote in message
news:em**************@TK2MSFTNGP12.phx.gbl...
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

Nov 18 '05 #2
> 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
Nov 18 '05 #3
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
--
MY ASP.Net tutorials
http://www.openmymind.net/
"Darrel" <no*****@nospam.com> wrote in message
news:OP**************@TK2MSFTNGP14.phx.gbl...
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

Nov 18 '05 #4
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
"Darrel" <no*****@nospam.com> wrote in message
news:em**************@TK2MSFTNGP12.phx.gbl...
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

Nov 18 '05 #5

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

Similar topics

10
by: Matthew Sims | last post by:
I've been performing search after search all over the internet reading up on all topics about making PHP secure with MySQL. There's a lot out there and not many concrete examples on how you should...
2
by: Hugh McLaughlin | last post by:
Hello Everyone and thanks for your help in advance. I have read a great deal about code reuse and the development of the three-tier application, but am somewhat confused on some issues and am...
11
by: DrUg13 | last post by:
In java, this seems so easy. You need a new object Object test = new Object() gives me exactly what I want. could someone please help me understand the different ways to do the same thing in...
4
by: Jiho Han | last post by:
What is the best way to check whether the page is simply a postback or the form has been submit with the intention of doing something? In the olden days, I used to check for a form field name...
10
by: jojobar | last post by:
Hello, I am trying to use vs.net 2005 to migrate a project originally in vs.net 2003. I started with creation of a "web site", and then created folders for each component of the site. I read...
8
by: Alvin | last post by:
Right. I`m using Dev-Cpp, and I`m working on a game at this time in SDL (http://libsdl.org), and I`ve come across this error: ------------------------------------------ main.c: In function...
10
by: Mike Logan | last post by:
I am using the "contract first" design methodology. Contract First is design the WSDL first then design the server and client. However I must design my XSD/XML Schema before anything. I am...
68
by: Jack | last post by:
http://www.open-std.org/jtc1/sc22/wg14/ http://www.open-std.org/jtc1/sc22/wg14/www/docs/n1135.pdf Has anyone gone through this? Is this useful? Will it make it to the next standard?
0
by: amitvps | last post by:
Secure Socket Layer is very important and useful for any web application but it brings some problems too with itself. Handling navigation between secure and non-secure pages is one of the cumbersome...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.