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

 

[Manohar Kamath [MVP]]

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/advanced_sql_injection.pdf

 

[Aaron Bertrand - MVP]

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?tablename=foo;TRUNCATE+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.

 

[Ray at]

http://www.nextgenss.com/papers/advanced_sql_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

 

[Jeff Cochran]

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/advanced_sql_injection.pdf

Now this is a great resource. Thanks.

Jeff

 

[Bob Barrows]

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