472,328 Members | 1,131 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,328 software developers and data experts.

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 2253
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: NotGiven | last post by:
Steve wrote, > "And read up on "sql injection" attacks (use your favorite search > engine). As indicated, validate input. e.g. if you expert...
1
by: Cogswell | last post by:
I am working on an ecommerce app and want to be able to take my entire POST results as one item (or iterate through them) and check for any...
11
by: BTR | last post by:
I have been working on this particular project for a little over 2 weeks now. This product contains between 700-900 stored procedures to handle just...
10
by: bregent | last post by:
I've seen plenty of articles and utilities for preventing form injections for ASP.NET, but not too much for classic ASP. Are there any good input...
8
by: stirrell | last post by:
Hello, One problem that I had been having is stopping email injections on contact forms. I did some research, read up on it and felt like I had...
7
by: | last post by:
There are assorted "SQL Injection vulnerability assessment tools" out there. They scan your site and send your report. They also take your money. ...
3
by: =?Utf-8?B?Um9kbmV5IFZpYW5h?= | last post by:
IIS 6 SQL Injection Sanitation ISAPI Wildcard at http://www.codeplex.com/IIS6SQLInjection I created an ISAPI dll application to prevent SQL...
2
by: Sudhakar | last post by:
A) validating username in php as part of a registration form a user fills there desired username and this is stored in a mysql. there are certain...
12
by: shank | last post by:
I've been hit again using DW, parameterized queries and stored procedures. I'm guessing I was not strict enough with character counts and allowing...
2
by: Brian Bozarth | last post by:
This is weird, I'm pretty familiar with SQL Injection - but we're getting these weird injection that is writing in the default document or home...
0
by: tammygombez | last post by:
Hey fellow JavaFX developers, I'm currently working on a project that involves using a ComboBox in JavaFX, and I've run into a bit of an issue....
0
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
1
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...

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.