JOIN screwing things up.
-
Nope... not the NULL thing. My explanation is probably going to sound a little "dated" because I gave up the "GUI world" about 5 years ago when my boss (actually, I'm the one that got her hired... hired my own replacement
) told me to create "danger" fields with Red lettering on a certain color Pink (was more like Fusia) background and warning fields with, get this, White lettering on a pale Yellow background.Anyway, there's a couple of techniques I've seen that work very well so long as they're not used for logins or other public-facing procs (used mostly for in-house configurable reporting, even then, some precautions must be taken to prevent SQL Injection). The only big difference between the two is that one is done using "embedded SQL" and the other is using reporting procs that take parts of SQL as parameters. In either case, it breaks down to "dynamic SQL"...
In both cases, parts of a query are formed outside of SQL. There can be many parts to the WHERE clause, or none if they want everything. To simplify their lives in figuring out what gets an AND and what doesn't, the first part of the WHERE clause is always "WHERE 1=1". Since that will always be true, if they include no other criteria, they get everything back. If they want to add criteria, instead of having to figure out that the first condition needs no AND after a WHERE, the WHERE 1=1 allows every criteria to start with an AND, even the first condition. I've seen folks do similar things using ORDER BY '1' (single quotes required so won't sort on 1st column) so they don't need to figure out if the rest of the ORDER BY list items needs to start with a comma or not.
It's very effective if you are one of those that use dynamic SQL, embedded or otherwise, to build queries using a single process with infinitely configurable criteria and sort orders. It can also be very, very dangerous... SQL Injection...
So far as SQL Injection goes, there are some techniques in login queries that hackers use to (many times, successfully) gain access to data... one way is to put OR 1=1 in the user name and password fields of a login screen (I left some special characters out but you get the idea). Most programmers are aware of this type of "attack", now-a-days, so they no longer use dynamic SQL in login code. The only time you'll see it actually listed is if you have some form of attack monitor that looks for these types of attacks before executing the SQL and, even then, it's very rare for 1=1 to immediately follow the WHERE.
A bit more malicious... if an attacker can figure out or guess a table name, they'll add something like ;DELETE tablename or '+'DELETE tablename' to open text criteria in an attempt do destroy data (again, left some special characters out... attackers have enough info, already). If you want to know more about how to prevent such attacks and why people make such a fuss about dynamic SQL, do a search for "SQL INJECTION" on Google... you'll be amazed at how simple it is to bust into someones data through the web or a local program unless someone has gone out of their way to prevent it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply