469,903 Members | 1,414 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

sql injection

gdp
hi...when guarding against sql injection attack from modified form or
querystring variables is it enough to strip out just apostrophes...if the
variable USERNAME is the name of a text box passed to a script is the below
always safe...

q1="select * from TABLENAME where username='" &
trim(replace(request("USERNAME"),"'","''")) & "'"

thankyou for all help given

regards

gdp
Jul 19 '05 #1
5 2137
In my opinion, this should pretty much solve the common SQL injection
attacks. The following document seems to agree, plus has extensive coverage
on the topic:

http://www.nextgenss.com/papers/adva..._injection.pdf

--
Manohar Kamath
Editor, .netBooks
www.dotnetbooks.com
"gdp" <gp********@blueyonder.co.uk> wrote in message
news:rq***************@news-binary.blueyonder.co.uk...
hi...when guarding against sql injection attack from modified form or
querystring variables is it enough to strip out just apostrophes...if the
variable USERNAME is the name of a text box passed to a script is the below always safe...

q1="select * from TABLENAME where username='" &
trim(replace(request("USERNAME"),"'","''")) & "'"

thankyou for all help given

regards

gdp

Jul 19 '05 #2
Not necessarily, there are other obscure scenarios, usually surrounding
techniques you shouldn't be using anyway. Imagine this:

CREATE PROCEDURE dbo.getfoo
@tablename VARCHAR(32)
AS
BEGIN
SET NOCOUNT ON
EXEC('SELECT * FROM '+@tablename)
END
GO

Then from ASP:

<%
set rs = conn.execute("EXEC dbo.foo '" &
request.querystring("tablename") & "'")
%>

Then hit this with:

http://www.yoursite.com/yourpage.asp...CATE+TABLE+foo

No apostrophes to replace, so even if you did your little replace method,
the table would still get truncated. This is certainly something that a
knowledgeable user could try, if you allow them to know the names of tables
(which they have no real need to know) and allow them to enter such names
unchecked.

Of course you could prevent this as follows:

CREATE PROCEDURE dbo.getfoo
@tablename VARCHAR(32)
AS
BEGIN
SET NOCOUNT ON
IF OBJECT_ID(@tablename) IS NOT NULL
EXEC('SELECT * FROM '+@tablename)
END
GO

The main thing is to avoid potential scenarios where a string can be
executed unchecked and un-type-verified. See
http://www.sommarskog.se/dynamic_sql.html for other perils of using dynamic
SQL in a stored procedure.

Then, avoid dynamic SQL in your execute string in ASP as well, as much as
possible. For anything remaining, the replace of ' should be sufficient.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"gdp" <gp********@blueyonder.co.uk> wrote in message
news:rq***************@news-binary.blueyonder.co.uk...
hi...when guarding against sql injection attack from modified form or
querystring variables is it enough to strip out just apostrophes...if the
variable USERNAME is the name of a text box passed to a script is the below always safe...

q1="select * from TABLENAME where username='" &
trim(replace(request("USERNAME"),"'","''")) & "'"

thankyou for all help given

regards

gdp

Jul 19 '05 #3

"Manohar Kamath [MVP]" <mk*****@TAKETHISOUTkamath.com> wrote in message
news:OP**************@TK2MSFTNGP10.phx.gbl...
http://www.nextgenss.com/papers/adva..._injection.pdf


I love this. I feel like I know ten times as much about SQL injection as I
did a few hours ago now. Thank you Manohar.

Ray at home
Jul 19 '05 #4
On Thu, 22 Jan 2004 15:50:21 -0600, "Manohar Kamath [MVP]"
<mk*****@TAKETHISOUTkamath.com> wrote:
In my opinion, this should pretty much solve the common SQL injection
attacks. The following document seems to agree, plus has extensive coverage
on the topic:

http://www.nextgenss.com/papers/adva..._injection.pdf


Now this is a great resource. Thanks.

Jeff
Jul 19 '05 #5
Manohar Kamath [MVP] wrote:
In my opinion, this should pretty much solve the common SQL injection
attacks. The following document seems to agree, ...


It does? To me, it seems to be saying that this method (escaping quotes) can
be defeated.

IMO, based on what I've read, the most foolproof way to avoid sql injection
is to avoid dynamic sql, whether that dynamic sql is created in asp code or
in a SQL Server stored procedure (sp_ExecuteSQL can be used to parameterize
dynamic sql statements in stored procedures). Passing parameters correctly
to a stored procedure that does not use dynamic sql will prevent all the
examples of injection I've seen from working. The pdf seems to agree with
this.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by stirrell | last post: by
3 posts views Thread by =?Utf-8?B?Um9kbmV5IFZpYW5h?= | last post: by
2 posts views Thread by Sudhakar | last post: by
12 posts views Thread by shank | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.