March 21, 2008 at 2:30 am
I have been using in my apps dynamically built sql with wrapper functions like this:
function sc_string (s as string) as string
sc_string = left(replace(s, "'", "''"), 8192)
end function
function sc_date(s as string, defval as string) as string
if isdate(s) then
dim m : m = cdate(s)
sc_date = "'" & year(m) & "-" & month(m) & "-" & day (m) & "'"
else
sc_date = defval
endif
end function
My question is if I strictly wrap any input params this way,
is there a way how to insert a code? I mean any less known
escape sequences or anything else??? Is there a way?
Many thanks in advance!
March 21, 2008 at 9:49 am
Well, there is ";" which is an end of statement character "--" which is a comment.
As a dba I do not like application built dynamic sql because it is harder to performance tune, you need to grant direct rights on tables, and it requires a rebuild of the application whenever bugs are fixed or performance issues are fixed.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 22, 2008 at 9:06 pm
I'm not quite clear how you're using this as an input parameter. Perhaps an example?
I would avoid dynamic SQL since it is harder to tune or make adjustments if that's what you're doing.
March 24, 2008 at 5:35 am
I have been using dynamic SQL in many of my stored procedures since the early 90's. I started with Ingres on a VAX, but have used it in Oracle and SQL Server as well.
All my code has been on private or stand-alone networks, so we didn't worry a lot about SQL Injection. If someone in the company had enough time to play around and try to hack our computer systems, then their job position could be eliminated.
With that said, I always wanted a better way to allow flexibility. After reading the link in SQL ServerCentral's E-mail from March 20, 2008 about 'Using CASE expression instead of dynamic SQL', I have been retrofitting much of my code to use the CASE statement. I was unaware that it could be used in so many situations. And most classes I have ever taken veer away from teaching the 'slick' and 'out-of-the-ordinary' topics.
Anyway, here's the link: http://www.mssqltips.com/tip.asp?tip=1455
I think this would produce a cleaner looking code-base. It may run faster in some situations.
-- Al
March 25, 2008 at 3:49 am
Thank you all for your replies. What I want is to find out if there is a magic way how to deal with escape sequences. Correct me if I am wrong but if there is a string value and wrapper/checker function replaces all ' with '' and enclose it into ' then there isn't no way how to insert code?
"select a, b, c from d where e = '" & x & "'"
is bad, because x can be "'; sp_msforeachtable truncate @;--"
but
"select a, b, c from d where e = " & sc_string(x)
make injection useless: '''; sp_msforeachtable truncate @;--' right?
BTW, if you use a smart class to handle parameters programmatically then you can see in query analyzer exactly the same result - i.e. smart class handling parameters is just a black box, which makes debugging harder because you can not see final command directly.
Am I right or wrong? Is there a real "added value" of smart classes from technology provider or it just doing the same things like my own wrapper and takes control over my program?
You know, if someone tell me "don't do that" - I am always asking "why?" and I never buy answers - "because I say", "because it is good", "everybody do that", etc. Things just work or not - so simple it is. If I have a doubts about claiming then I check it. I compare, sort and combine any information and very, very often there is sulphuric smell of advertisement bullshit in the air - that is my opinion.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply