- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
CALL Query3('SPTST0001', '(SELECT a,b,c FROM x,y,z WHERE d=1 AND e='test' ORDER BY z.c)');
Not Query1?
Admin
The real WTF being of course that a DBA became manager...
Admin
Shouldn't there also be 'test'?
Admin
CALL Query3('SPTST0001', '(SELECT 'This is my comm
ah forget it.
Admin
Nice, but I've just recently read that one before. Anybody remember where?
Admin
"Your wheel design is all very well and good, but my 'wheel v2.0' is going to be way better..."
Admin
Hi dawg, I heard you like stored procedures, so I put a sql statement inside of a sql statement, so you can query while querying!
Admin
How can you NOT go on a violent rampage in a work environment like that?
I'm a sysadmin doubling up as DBA for a smaller company, and our work isn't all that brilliant either, but this? My god... Someone needs to be put against a wall and shot.
Ugh! At least at my current job we're doing our absolute best to remove all the crap from our SaaS app (the basic app was written by external consultants who were pretty much unconcerned with maintainability of the system, so we've got our share of WTF worthy stuff). How can you possibly call yourself a DBA (or ex-DBA in the case of the manager) if you support this kind of ****?
Admin
Congrats! You get the Golden Code Monkey of the Day award!
Admin
I've seen this sort of thing around. People hear that stored procedures are more secure, so they choose to use stored procedures - and then proceed to concatenate strings within those stored procedures anyway. It's a brilliant way to completely miss the point, and very WTF-worthy.
Admin
CALL Query1('SPTST0001', '(DELETE FROM x,y,z)');
Gotta love good security.
Admin
Ok, but how do they know when to call each query? Was it Query1() or Query3()?
Admin
Nice.
Capthca = Cogo.. Torgo's cousin.
Admin
I'm fairly certain that 'SELECT * FROM (DELETE FROM x,y,z')' would return a syntax error
Admin
For the love of Buddha... Workaround "Vogons" Beaurocracy with SQL Injection...
Admin
A DBA who cares more about procedure than security..WTF?!!
Admin
Highly excellent. Pimp my SQL.
Admin
insert into RawQuery(PROC_ID, PROC_QUERY_SQL) values ('SPTST0002', '%1%')
Much more flexible :)
Admin
No, no. Query1 takes 6 parameters. Everybody knows that! Didn't you get the memo?
Admin
Not an expert, but wouldn't:
('SELECT * FROM table1;DELETE FROM x,y,z')
work?
Admin
Man, I was so sure that I was going to be the only person anal enough to notice and comment on that. And there it is in the first freaking comment. Hat's off, sir.
Admin
When developers have to take advantage of security flaws in order to do what they need to do to get the job done, it's time review your architecture. And fire somebody.
Admin
Not in a stored procedure.
Admin
Shouldn't this be titled "Revenge of SQL Sentences"?
Admin
I've not seen that before, but there are similar goings on in lots of companies. Someone comes up with some procedure to deal with a real or perceived problem. It's daft, but the person who came up with it doesn't want to admit to that, and/or the staff don't want to tell their manager he's wrong.
So eventually people find a work-round, and everyone is happy. The developers can do their job, while having a laugh about the stupidity, and the manager doesn't feel challenged as the procedure is still in place.
Occasionally a new manager will come in, discover the WTF, and suggest removing it. But by then everyone is used to the status quo, and quotes the cost of replacing a big legacy system, and some new issue comes up to take the new manager's time, so the WTF stays in place until long after anybody can remember the original reason.
Corporate culture, good or bad, is very hard to change with anything short of bankruptcy, or mass redundancy in the department concerned.
Admin
How dare you accuse your consultants of being "unconcerned"! They were very concerned about the maintainability of the system. They wanted to be paid to maintain it and, to improve the odds, they tried to make sure that no one else could. Characterising that as "unconcerned" is just...well...WTF?
Admin
Actually, the wheel has been through many revisions. It went to a perfect circle made out of wood, to having a rubber exterior, to having pneumatic cushioning, to mag wheels (which lower the weight of the wheel), to spinning rims.
Okay, the last may be a counter-example, but wheel technology has upgraded over time.
So have arrays, with actually checking the bounds instead of reading random memory.
"Reinventing the wheel" is only bad if it isn't necessary. But to get to mag wheels, you had to go back and complety redo the construction a wheel from based on wood, spokes and hoop to a metal disk.
Admin
Ditto
Admin
-- third try --
Admin
I don't think ORDER BY in a sub-query is good SQL. It's certainly not guaranteed to persist to the eventual result set.
Admin
This reminds me of a DBA I used to work with. She wanted EVERYTHING in stored procedures, and she wanted to review all of them before they went to production. So, I would write a stored procedure and send it to her for review. Every time, I got a note back along the lines of "I don't understand your procedure... does it work?", to which I always replied yes, and she pushed it to production. Between that and the fact that she always called me to fix the things she broke, I finally left the company after a little over 2 years. She's still holding up progress there.
~md5sum~
Admin
Admin
Why is it that so many IT companies have small "elite" groups of people who firmly stand, and continue to stand, in the way of production? I have seen Testing departments who's testing was worthless yet jacked internal costs through the roof.
Network admins who wanted to provide the least amount of network they could get away with. "Why do you need that port?" "Because without port 22 we can't access the client servers which we were hired to work on." "Can't they use a port we have open?" "Not really. Why do you block outgoing ports anyway?" "You wouldn't understand." "Actually I contributed to the OpenSSH project so try me." "I don't have time to explain."
MIS people who strive to be assholes. "You can't have BSD installed on your development server as we haven't approved BSD." "But our 20 Million dollar client uses BSD and thus our software must be developed and tested in an identical environment." "We first have to examine the BSD system for security flaws. It has been hacked before." "So we should stick with Windows Server because it is better?" "Yes I have a white paper proving it is."
The solution to all of the above is to go to the sales person and tell him that we won't be billing on his project until the Testing, IT, MIS, DBA, ... department manager gets his head out of his ass. Usually the problem is cured within minutes and another department head hates me.
Admin
And what about SQL injection safety with that approach?
Some solutions seems to be worse than others.
Admin
Sorry to state the obvious, but here you need this:
('SELECT * FROM table1;SELECT FILE_NOT_FOUND;DELETE FROM x,y,z')
Captcha ideo:related to an idea, but incorrect (see the DBA from this post for examples).
Admin
It's all fun and games until the DBAs remove SPTST0001 which was, after all, only a test query. Then they're going to bet on how many developers' heads they can make explode.
Admin
If that stored procedure is putting together an ad hoc SQL statement and executing it, then you could use the semicolon and perform a SQL Injection attack. The exact syntax "DELETE FROM x,y,z" might not be good but you could perform an arbitrary command.
Of course you usually protect from SQL Injection by end users. If you have to protect from your developers you're kind of screwed anyway. Or if the developers are letting invalidated user input make it through to be used in ad hoc SQL, you're screwed even if you have a much less WTF structure in place anyway. So, my verdict would be that the SQL Injection concerns are low on the very tall WTF totem pole here.
Not by the ANSI standard, no, although a lot of the time the query optimizer might give that to you. What's even worse is that it might inconsistently give you the order you're expecting, so it works for your test cases then "randomly" fails later in production.
Admin
Hey, don't forget steel wheels (on trains), hubless wheels (funky new concept), casters, and various gears.
Lots of things to do with wheels.
Admin
Our industry is not immune to a$$holes by any means. In our dept the first thing we use to gauge someone is how long it takes them to say "I don't know" to something. No one is an expert at everything. If you go much more than your first week without saying IDK, you are probably one of those dumba$$es who will take down an entire system rather than just admit you don't know, thereby causing more downtime and frustration. In our IT dept you don't get in trouble for not knowing. If we assign you something you are not able to handle, just say so and we will work it out. I am amazed at the damage caused by someone who is too afraid to admit that they don't know it all.
Admin
We have that problem here at Microsoft. It's called the Windows Division :-)
Admin
Keep in mind that they may be gauging you too - lots of places will penalize you for not knowing and bothering other devs instead of locking your door and figuring it out.
Admin
One of you has to know this. Is there an actual name for this kind of for-the-ease-of-maintenance approach? I've not only seen this in action before, but I seem to remember reading some academic paper about it too. I've had this conversation too many times...
Some other IT person: "I've come up with this great solution. It's very flexible and it's going to save us a lot of time."
Me: "OK, but you're trading more flexibility for making the code itself much harder to read and actually making it much less efficient."
Some other IT person: "Uh, but...didn't I mention that it's very flexible?"
Not that I expect to win this kind of argument just by whipping out some 50 cent term, but it'd be nice to have more to back up my position than just my experience (and common sense.)
Admin
The inner platform approach. The database (assuming it is not a toy RDBMS) already has a mechanism for calling stored procedures with parameters, and for displaying the stored procedures for the DBA to review, but the DBA has replicated it on top of the database.
Admin
This being a specific case of an "anti-pattern." It is a very common problem with database developers when you have clever developers who do not understand the strengths of a an RDBMS.
Admin
Admin
I store project queries in a table, but I don't just toss them in there. I have a separate query development environment, with a query builder, query editor, query tester, search, print, save/load to/from text file, etc. I also use a library of simplified calls to load and execute the queries from the applications. I find this much simpler than testing an application and its embedded queries at the same time. YPMV.
-Harrow.
Admin
With procedures like this, one must take drastic action in order to show the Vogons who drew up such procedures, that you still can do your normal job. This I believe is the synthesis of Vogon poetry. One must work around the procedure in order to make the Vogons happy in the sense that you are busy working for them and complying to their whims.
Admin
It's amazing how many people assume they are smarter than everyone else (or at least think they're the only ones that ever notice anything....)
Admin
I don't think he believes that as much as he believes he's the only one who would care enough to mention such a little nitpick...
There's a difference between being smarter and being more pedantic. Only those who are the latter don't know the difference. :)
Admin
The 'elite' groups are too often a result of incompetent management. While highest management only needs to understand fairly high-level concepts, the closer a manager is to the Techs/Devs the more technical knowledge they should have themselves (This is true in any industry - The big boss of a chain of garages should at least understand that they make money from fixing cars, and probably that cars have combustion engines. A manager of a local garage/workshop doesn't need to know the intricacies of the combustion engines, but a reasonable understanding of at least what the major components do and how they interact is very useful. A leading hand, on the other hand should have a reasonably good grasp of how various components work, and should at the very least be able to advise mechanics against bad practice, even if he isn't himself an expert on that particular component). Managers not understanding the technologies within their groups leads to devs (in particular contractors/consultants, I suspect) acting elitist and claiming to be far more knowledgeable than they are (because they will never be challenged). Assuming they get work done (irrespective of the quality) the managers eventually begin to believe that they really are as good as they claim to be - and will support them when junior devs question why things are done so silly.
Drifting further off topic, this is also why IT costs so much. A dev (perhaps over-ambitiously) quotes a job at 5 days (although in my experience, this usually means that they think they'll do it in 2 days, but are arrogant enough to think that others would take 5, so they can have 3 days of reading TDWTF). The immediate Team Lead barely understands the problem (and certainly not the proposed solution) and quotes 10 days to his Manager (just to be sure), and adds 5 days testing, for a total of 15 days. The Project Manager (who is even further detached, and doesn't want to look bad if things go awry) doubles this quote to 30 days (and probably adds some caveat to allow him an excuse to fail - within 30%, say). The Program Manager (who has some limited knowledge of the budget available), and doesn't understand at all that only one line of code needs changing quotes for 50days (10 weeks is a nice round number). The Director needs to pass this on to the business/client and (just to make sure that all contingencies are covered) decides to go for 4 months, and rounds this off to 80 days. He then realises the budget should stretch fo rmore, and decides on 100 days (and reduces the escape clause to 15% - 30% sounds like our devs are incompetent). So far, we have a piece of work (which the original dev thought would take 2 days) which (including testing) has been quoted at 100 days. Keeping in mind that (for support work, at least) the bulk of the work is actually in the quote - you have to work out roughly what the problem is and how you will fix it before quoting), so the code is probably finished it just needs to be inserted, commented and refined slightly, we basically have very little to do and 100 days to do it in. So why do IT Projects frequently run over time/budget? The original developer leaves sometime between quoting and and the quote finally being submitted to business (and in leaving, never bothers to submit his code changes to version control - it's not approved work, after all). The new developer is one of two types.
Type A realises that this quote is way OTT, and spends 8- days on facebook, before even looking at the problem. He discovers it's a little more complex than he expected, and wastes about 4 days working out the background and exactly what needs to be done (he is probably unfamiliar with the application at this stage). He spends a further 3 days making the change. Type B is a recent grad, who think he knows everything, but given that there are 100 Days to do this change, it must be more complicated than it appears on face value (and since management is obviously expecting a complicated solution, we may need to overcomplicate the problem if we can only think of a simple solution). Then redtape and politics delay the projects movement through the testing environments (meanwhile, everyone is charging to this project, even if there isn't actually any work going on), and we finally get to the testers on day 95. The testers (though originally allocated 5 days) are in a panic, because a 100 day project is only with them for 5 days (or 1/20th of the time for dev). Problems may or may not be found, but we can guarantee that this release will not make it into production on time....
All time sheets show that this project took over the 100 days planned, yet very little work has actually happened. The problem is that the managers (who didn't understand what was going on) kept adding time, and the devs decided if they had the time allocated they'll use it. A good manager would (hopefully) present the dev's with a timeline (based on this over-quote) that was much tighter - 20 days, say, and then allow the project to slip if required. The devs never need to know anything more than their team leads quote for time.
I digress, maybe I've worked in the public service too long sigh