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
