Trying to optimize a WHERE clause

Question

Trying to optimize a WHERE clause

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Ray K SSC-Dedicated Points: 31163 More actions · Answer 1

ColdCoffee (6/3/2010)
Ray K, nice that you have fixed the code, but for soehting called SQL Injection, you will still need to tweak the code a bit..

Yeah, I read the articles a while back, and I'm familiar with SQL injection. I thought parameters were supposed to thwart that (to an extent). Is there something I'm missing?

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Ray K SSC-Dedicated Points: 31163 More actions · Answer 2

Okay, I see. They're not the articles I thought they were. That's what happens when I skim through stuff rather than actually reading them! 🙂

If I remember correctly (I'll check to make sure), I believe we do make use of aliased logins/impersonation with secure settings. I believe they're set to read-only.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Ray K (6/4/2010)
Is there something I'm missing?

Nope. There's no concatenation of user-inputted values directly into the string, so that's pretty safe as you've done it.

Just one small point, you don't need the rtrim before comparing with ''. SQL ignores trailing spaces when doing string comparisons.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ray K SSC-Dedicated Points: 31163 More actions · Answer 4

I just re-read Gail's first article. I am not concatenating ANY input parameters as strings; they are all being passed as parameters. The only concatenation that occurs is when the code checks to see if a parameter value exists, then it appends it to the WHERE clause. But it is NOT appending the parameter; it is appending the condition (e.g. 'NAME like @Name').

So I don't THINK there are any SQL injection issues in that respect -- unless I'm missing something (which is always possible) . . .

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Ray K SSC-Dedicated Points: 31163 More actions · Answer 5

GilaMonster (6/4/2010)
Ray K (6/4/2010)
Is there something I'm missing?
Nope. There's no concatenation of user-inputted values directly into the string, so that's pretty safe as you've done it.
Just one small point, you don't need the rtrim before comparing with ''. SQL ignores trailing spaces when doing string comparisons.

Hi Gail -- I was in mid-type when you replied! (Nice articles, as always, BTW!)

Yeah, the rtrim is more old habit than anything else. Me and my structured programming background . . . 😀

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/