The Daily WTF: Curious Perversions in Information Technology

Richard Nixon · 2005-08-19 Reply Admin

Anonymous:
Ok, I'll admit to writing a sproc similar to this, but there is a perfectly good reason for this. My former employer, who will go unnamed (but basically Germany's largest bank), put a procedure in place where all the procs had to be reviewed and approved by at least 2 different groups (one of which didn't have a foggiest idea of what SQL was and the other one merely knew how to put together a basic SELECT) before being commited to the database. This had to be accompanied by a multitude online forms, all of which had expiration dates. If one of the groups didn't approve it in time, you had to resubmit. So having gone through a couple of these sproc submissions, I had enough and simply wrote a proc like the one above. As I expected, the sproc got approved and never had to submit another sproc again.

Chances of SQL Injection attack on my apps? All of them were internal, the likelyhood is small. And if it happens, that's just the cost of putting stupid policies in place. Would serve them right.

Having code reviews is a stupid policy? That doesn't seem right. It sounds like the problem at that company wasn't the policy in place but the people employed there.

brazzy · 2005-08-19 Reply Admin

Richard Nixon:

Having code reviews is a stupid policy? That doesn't seem right. It sounds like the problem at that company wasn't the policy in place but the people employed there.

If the policy requires code reviews before you can even test the code (which is a serious impediment to getting anything done) AND does not ensure that they are performed in a timely manner by people with expertise in that field, then the policy IS a large part of the problem.

IMO one of the most annoying parts of development work are the times when you're under pressure by a deadline and yet have to wait for some red tape procedure to complete before you can continue. Of course then you're expected to work on some other parts of the project, but those come with their own red tape, and of course switching between tasks often incurs both mental and technical overhead...

dhromed · 2005-08-19 Reply Admin

I think this ranks among the most WTF of WTF's I've ever read here.

It's so simple, and yet, apparently, in the guy's mind, it does not compute.

...how is that possible?

tufty:
Otherwise, yeah, hacking into a box with SQL Server on it is not terribly difficult by all accounts. To be honest, most of the web app installations I've come across using SQL Server use 'sa' as the account to connect as, for fuck's sake. Simon

Yeah, that's MSSQL's fault.

Mung Kee · 2005-08-19 Reply Admin

Anonymous:
Ok, I'll admit to writing a sproc similar to this, but there is a perfectly good reason for this. My former employer, who will go unnamed (but basically Germany's largest bank), put a procedure in place where all the procs had to be reviewed and approved by at least 2 different groups (one of which didn't have a foggiest idea of what SQL was and the other one merely knew how to put together a basic SELECT) before being commited to the database. This had to be accompanied by a multitude online forms, all of which had expiration dates. If one of the groups didn't approve it in time, you had to resubmit. So having gone through a couple of these sproc submissions, I had enough and simply wrote a proc like the one above. As I expected, the sproc got approved and never had to submit another sproc again.

Chances of SQL Injection attack on my apps? All of them were internal, the likelyhood is small. And if it happens, that's just the cost of putting stupid policies in place. Would serve them right.

I hope you're referring to the policy of having it reviewed by groups ignorant of SQL, and not code reviews in general. Lack of code reviews is one of the reasons I loathe working for consulting companies. All they care about is getting their noxious programmer droppings out the door, without regard for quality or the handful of sev1or sev2 defects in production.

dubwai · 2005-08-19 Reply Admin

cjs:
dubwai:

A stored procdure is a great way to create clean interfaces between code and the DB. It's a lot easier to refactor code to call the same stored procedure in a new DB than to refactor all the SQL in your code. In Java, for example, it's just a matter of changing drivers to switch DB vendors if you only use stored procedures. It also insulates the code from table structure changes.

It depends, actually. In a lot of my systems, it's much easier to go through the code and refactor embedded SQL than it is to change stored procedures. This is mostly because it's easier to write and change Ruby code than it is to write and change SQL code.

But it's the DBAs tha have to do the work. [;)]

dubwai · 2005-08-19 Reply Admin

AC:

Portability is a good point but I've never been able to change a schema and NOT change the SPs. Plus the poster is just saying that a lot of ORM systems have a hard time with SPs. I agree.

Here:

http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#sp_query

Interesting. It seems like a defect of Hbernate more than anything else, though.

I'm not saying you don't have to change the SPs, I'm suggesting that you should generally be able to avoid changing the interface and code.

PhiloChronos · 2005-08-19 Reply Admin

hank miller:
Wtf? Code reviews? Worried about injection attacks? What universe does Louis work in? I want to switch. Don't they know that code review is something to pay lip service to, but is too hard to actually do? The few times I've seen it, I've had to force it down their throats.

Here, fresh out of school, and I wrote this code, will you review it and tell me if I'm doing ok? "Looks like code written in our language and it seems like it will compile - commit it." Come to think of it, those guys quit a few months latter, and I ended up maintaining their code - it would have been sent here if this site existed back then.

There's not even any lip service paid to such things where I work.

Here, it's all:

Them: Can you program?

Me: Yeah, sure...I guess.

Them: Good. These people want to add such and such functionality to their system. Go do it.

Me: Ok. Where can I find the requirements document for the existing system, and what are the procedures for adding requirements?

Them: ?

Me: Requirements document. The document which tells us exactly what the system they desire has to do before they'll be happy and give us money.

Them: ?

Me: Ok, how was this system designed then? How did you know what they wanted the system to do?

Them: Oh, just whenever the users told us they wanted something, we did it.

Me: Oh...well then...uhh... I'm going to go ahead and schedule some meetings with the users and their boss where we can determine explicity what it is they want added to their system. Then I'll write up a requirements document for them to review and sign if they're happy with it. Does that sound good?

Them: No no no no...that's not your job. They come up with what they want you to do, and then you do it.

Me: Al...right...I guess...ok...but...hmmm. Who will I be working with, and how often do you all usually do code reviews?

Them: ?

Me: Code reviews. Typically where developers review other developers' code to make sure everything looks alright, is on right track, is on schedule, etc.

Them: ?

Me: Being just out of college, I'd appreciate it if some developers here with more exeprience in general and more experience with this company's systems specifically could review my code in case I do something worng due to lack of experience.

Them: We thought you said you could program.

Me: ... ... ... nevermind then. I'll get right to work on this.

Them: Good.

I then got the "requirements" from the users, which was full of gems such as:

"Want option to send payment customer with inventory report (ex) paid and pass button to link"

monkey@thekeyboard · 2005-08-19 Reply Admin

I always replied documa-what? Sometimes it took multiple repeats but they eventually got it.

WIldpeaks · 2005-08-19 Reply Admin

--------quote-------

How about this?

<FONT color=#000099>CREATE PROCEDURE</FONT> [SADynamicTPSBuilder]
(
  @SELECTS <FONT color=#000099>VARCHAR</FONT>(8000),
  @JOINS <FONT color=#000099>VARCHAR</FONT>(8000),
  @OTHER_NON_INJECTION_CODE_REALLY VARCHAR(8000),
) <FONT color=#000099>AS
BEGIN

  SET NOCOUNT ON
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  EXEC</FONT> (@SELECTS + @JOINS + @OTHER_NON_INJECTION_CODE_REALLY)
  
  <FONT color=#000099>RETURN</FONT>(-1)

<FONT color=#000099>END</FONT>

--------/quote-------

Yep, this is definitly the solution.

2005-08-19 Reply Admin

Uh boy!!!

[image]

Mung Kee · 2005-08-19 Reply Admin

WIldpeaks:

--------quote-------

How about this?

<font color="#000099">CREATE PROCEDURE</font> [SADynamicTPSBuilder]
(
  @SELECTS <font color="#000099">VARCHAR</font>(8000),
  @JOINS <font color="#000099">VARCHAR</font>(8000),
  @OTHER_NON_INJECTION_CODE_REALLY VARCHAR(8000),
) <font color="#000099">AS
BEGIN

  SET NOCOUNT ON
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  EXEC</font> (@SELECTS + @JOINS + @OTHER_NON_INJECTION_CODE_REALLY)
  
  <font color="#000099">RETURN</font>(-1)

<font color="#000099">END</font>

--------/quote-------

Yep, this is definitly the solution.

Do you really need 24k characters? I think 50-100 is PLENTY for this injection:

"DROP SCHEMA {name} {options}"

Richard Nixon · 2005-08-19 Reply Admin

brazzy:
Richard Nixon:

Having code reviews is a stupid policy? That doesn't seem right. It sounds like the problem at that company wasn't the policy in place but the people employed there.

If the policy requires code reviews before you can even test the code (which is a serious impediment to getting anything done) AND does not ensure that they are performed in a timely manner by people with expertise in that field, then the policy IS a large part of the problem.

IMO one of the most annoying parts of development work are the times when you're under pressure by a deadline and yet have to wait for some red tape procedure to complete before you can continue. Of course then you're expected to work on some other parts of the project, but those come with their own red tape, and of course switching between tasks often incurs both mental and technical overhead...

I'll have to respectfully say that you missed my point. You're arguing about implementation details. I am saying that code reviews are good things and looking for ways to circumvent them is a mistake. If there are problems with code reviews such as the wrong people doing the reviewing or the reviewing coming at the wrong time, these can be fixed. But reviews should still be present.

2005-08-19 Reply Admin

I remember attempting to implement a code review process at my past company. The Director of IT considered it too much trouble to code review as we coded, so instead we sacrificed ONE day a month to review a bunch of code ALREADY out in production with no plans to correct it. I guess the hope was they would do better next month with the knowledge of what was messed up this month.

Maurits · 2005-08-19 Reply Admin

Anonymous:
Anonymous:

The only thing he did was allow a 16k character sql statement instead of 8k.



Actually, not even that. If the database in question has an 8000 character limit on the varchars, as is quite likely, then concatenating two big strings that add up to over 8000 characters will truncate. So this is *even dumber* than it first appears.

Not in T-SQL, no. Even though there is a definite limit to the size of CHARs/VARCHARs, you can concatenate them and it won't truncate.

For instance (for Sybase, the limit is 255),
DECLARE @p VARCHAR(255), @q VARCHAR(255)
SELECT @p = REPLICATE('A',255), @q = REPLICATE('B',255)
SELECT SUBSTRING(@p+@q, 254, 4)
would return "AABB".

Actually there's some EXEC() magic going on here in T-SQL.

In T-SQL, there's a HARD-CODED limit of 8000 characters. varchar(8001) doesn't work.

The T-SQL analog of the code above also doesn't work:

DECLARE @p VARCHAR(8000), @q VARCHAR(8000)
SELECT @p = REPLICATE('A',8000), @q = REPLICATE('B',8000)
SELECT SUBSTRING(@p+@q, 7999, 4)

-- this returns 'AA', not 'AABB'

BUT... EXEC() bypasses this. See my earlier post in this thread.

bugsRus · 2005-08-19 Reply Admin

Mung Kee:
Sean Connery:
johnl:

...and how it would affect our planned buctracking/knowledge base system if we allowed free text searches.

Oh man. One typo away from butcracking.

Sean Connery: "I'll take Anal Bum Cover for 7000."
Trebek: "That's An Album Cover."

Sean Connery: "I'll take Penis Mighter for 1000."
Trebek: "That's Pen Is Mighter."

2005-08-19 Reply Admin

Anonymous:
Allowing only stored procedure access to a database is a huge WTF and should only be favored by psycho DBAs. It's like the cops fighting speeders by making everyone take the bus. Then only the bus drivers can speed. Just try and implement a sensible caching or object relational mapping strategy when you are in this scenario...there are lots of ways to prevent SQL injection attacks. Allowing only stored procedures is not one on its own, as this case proves. Who is to say that the consultant wasn't filtering for SQL injection attacks on the code that invoked the stored procedure?

If you want to be sure a stored proc isn't used for SQL injection attacks, you don't assume the procedure will be run the intended program/people. While circumstances may be such that this is virtually guarenteed I would still put as many checks as low in the system as possible. I can assume the program is going to maintain referential integrity too, but I'm more likely to set the database up with FK constraints in place.

2005-08-19 Reply Admin

tökk:
But then you lose the performance benefit stored procedures have (cached query plans etc)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0nxv.asp

ChrisR · 2005-08-19 Reply Admin

Burt Reynolds: "I'll take ape-tit for 1000"

Trebek: "That's a'petit for 400."

John Smallberries · 2005-08-19 Reply Admin

cjs:
dubwai:


A stored procdure is a great way to create clean interfaces between code and the DB. It's a lot easier to refactor code to call the same stored procedure in a new DB than to refactor all the SQL in your code. In Java, for example, it's just a matter of changing drivers to switch DB vendors if you only use stored procedures. It also insulates the code from table structure changes.


It depends, actually. In a lot of my systems, it's much easier to go through the code and refactor embedded SQL than it is to change stored procedures. This is mostly because it's easier to write and change Ruby code than it is to write and change SQL code.

You're joking, right?

It's easier to change the same SQL in 10 (or 100) places in hundreds of source files than to change 1 proc? And managing permissions on all the files with embedded SQL is also easy, right? And unit testing, portability, abstraction, query plan/data caching...wtf are you thinking?!

dubwai · 2005-08-19 Reply Admin

John Smallberries:

cjs:
dubwai:

A stored procdure is a great way to create clean interfaces between code and the DB. It's a lot easier to refactor code to call the same stored procedure in a new DB than to refactor all the SQL in your code. In Java, for example, it's just a matter of changing drivers to switch DB vendors if you only use stored procedures. It also insulates the code from table structure changes.



It depends, actually. In a lot of my systems, it's much easier to go through the code and refactor embedded SQL than it is to change stored procedures. This is mostly because it's easier to write and change Ruby code than it is to write and change SQL code.

You're joking, right?
It's easier to change the same SQL in 10 (or 100) places in hundreds of source files than to change 1 proc? And managing permissions on all the files with embedded SQL is also easy, right? And unit testing, portability, abstraction, query plan/data caching...wtf are you thinking?!

While I appreciate the backup, hopefully a system will not have the same SQL in 10 different places. On the other hand, this is what often happens anyway. Also, I suppose that a good stored-procedure can replace a few different selects. Anyway, I think your point is good, it just kind of looks like a strw-man on the surface.

2005-08-19 Reply Admin

I've never understood how SQL injection attacks would ever work in the real world. Who are these idiots that are building strings based on user input without escaping special characters?

Jeff S · 2005-08-19 Reply Admin

dubwai:
John Smallberries:

cjs:
dubwai:

A stored procdure is a great way to create clean interfaces between code and the DB. It's a lot easier to refactor code to call the same stored procedure in a new DB than to refactor all the SQL in your code. In Java, for example, it's just a matter of changing drivers to switch DB vendors if you only use stored procedures. It also insulates the code from table structure changes.



It depends, actually. In a lot of my systems, it's much easier to go through the code and refactor embedded SQL than it is to change stored procedures. This is mostly because it's easier to write and change Ruby code than it is to write and change SQL code.

You're joking, right?
It's easier to change the same SQL in 10 (or 100) places in hundreds of source files than to change 1 proc? And managing permissions on all the files with embedded SQL is also easy, right? And unit testing, portability, abstraction, query plan/data caching...wtf are you thinking?!

While I appreciate the backup, hopefully a system will not have the same SQL in 10 different places. On the other hand, this is what often happens anyway. Also, I suppose that a good stored-procedure can replace a few different selects. Anyway, I think your point is good, it just kind of looks like a strw-man on the surface.

dubwai -- It is much easier, quicker, more intuitive, and less error prone to ALTER procedures and work in SQL alone than it is to find, parse, TEST, re-compile, and redistribute 100s of lines of SQL= SQL + "xxxx" statements throughout *all* of the apps that use your database... I don't really know how anyone can really claim otherwise ....

Jeff S · 2005-08-19 Reply Admin

A few other points --

of course EXEC() is needed in general (mostly for DBA maintenance routines), but a well-designed (and normalized) system should never require a dynamic sql call for standard insert/delete/update/select operations.

* Always remember to distinguish between "standard" applications and report-writing applications when making statments like "always (or never) use dynamic sql (or stored procedures)". Report-writing applications are in a class of their own, and indeed often require the ability to construct and pass in SQL statements, but even those types of apps should also be making use of Views to at least provide some level of abstraction and security. Obviously, if a portion of your application allows users to design somewhat complex custom reports, your app is going to need to construct SQL statements. As with anything, this might need to be allowed, but should be carefully auditted and executed using accounts that can only SELECT from appropriate views (and/or tables) and not do much else to mess with the database.

* Someone mentioned something about dynamically crosstabbing data by using EXEC(), and asked how else can you do this dynamically in SQL Server? The answer is -- you don't! Crosstabbing is a presentation issue, not a database issue, and should rarely (if ever) be done with SQL. Generating resultsets with varying column names is completely the opposite of what RDBM's are supposed to be doing. See my blog for some notes on this: http://weblogs.sqlteam.com/jeffs/category/156.aspx .

dubwai · 2005-08-19 Reply Admin

Jeff S:

dubwai -- It is much easier, quicker, more intuitive, and less error prone to ALTER procedures and work in SQL alone than it is to find, parse, TEST, re-compile, and redistribute 100s of lines of SQL= SQL + "xxxx" statements throughout *all* of the apps that use your database... I don't really know how anyone can really claim otherwise ....

I'm not really sure why you are reiterating my point and addressing it back to me.

brazzy · 2005-08-19 Reply Admin

Richard Nixon:

I'll have to respectfully say that you missed my point. You're arguing about implementation details. I am saying that code reviews are good things and looking for ways to circumvent them is a mistake. If there are problems with code reviews such as the wrong people doing the reviewing or the reviewing coming at the wrong time, these can be fixed. But reviews should still be present.

Theoretically, you're right. Unfortunately, in practice a lowly developer often has no way to change policies, especially not quickly.

brazzy · 2005-08-19 Reply Admin

Jeff S:

dubwai -- It is much easier, quicker, more intuitive, and less error prone to ALTER procedures and work in SQL alone than it is to find, parse, TEST, re-compile, and redistribute 100s of lines of SQL= SQL + "xxxx" statements throughout *all* of the apps that use your database... I don't really know how anyone can really claim otherwise ....

Why do you think stored procedures don't have to be found, parsed(?), and TESTED? And if you have "100s of lines of SQL= SQL + "xxxx" statements throughout *all* of the apps that use your database..." then you're a piss-poor developer and would write godawfully crappy SPs as well, probably multiple ones for the same task with tiny variations AND vulnerable to SQL insertion.

A well-designed DB access layer will have each SQL statement in exactly one place, easy to find, reuse and refactor. There isn't really anything that SPs gain you in terms of maintainability compared with that.

2005-08-19 Reply Admin

Anonymous:
Anonymous:


The only thing he did was allow a 16k character sql statement instead of 8k.



Actually, not even that. If the database in question has an 8000 character limit on the varchars, as is quite likely, then concatenating two big strings that add up to over 8000 characters will truncate. So this is *even dumber* than it first appears.

Not in T-SQL, no. Even though there is a definite limit to the size of CHARs/VARCHARs, you can concatenate them and it won't truncate.

For instance (for Sybase, the limit is 255),
DECLARE @p VARCHAR(255), @q VARCHAR(255)
SELECT @p = REPLICATE('A',255), @q = REPLICATE('B',255)
SELECT SUBSTRING(@p+@q, 254, 4)
would return "AABB".

In MS SQL Server (which has an 8000 character limit on chars/varchars and 4000 on nchars/nvarchars), strings really are truncated. Example:

declare @x varchar(8000)
set @x = 'really long string (~6000 characters)'
select len(@x)
select len(@x + @x)

The first select returns ~6000, and the second returns 8000.

This will probably not be the case in SQL Server 2005, with its fancy new varchar(max) data type, but it definitely is in SQL Server 2000.

2005-08-19 Reply Admin

I'm sorry, this is too funny. I used to work with louis and I review sql code as well. I've worked with sql for a long time and there are very few reasons you have to use dynamic sql. The main one being a poor database design using such things as dynamic table names and other nonsense. Assuming you're developing the whole app, db included, there isn't any reason you couldn't design the whole thing to use only SPs to hit the db. We still have that rule where I work also. What's so funny is the WTF isn't obvious to some people on this board :-)

2005-08-20 Reply Admin

I'm nieve and curious. Can someone explain why is a stored procedure better then having a function in my program that runs the same SQL requests? Is there a difference between what you would put in the code and a stored procedure?

2005-08-20 Reply Admin

Well, an obvious one is if the database server and php/asp/what-have-you server are seperate machines...in the case of an SP, it doesn't have to bus over the network 42 times.

foxyshadis · 2005-08-20 Reply Admin

Anonymous:
I'm nieve and curious. Can someone explain why is a stored procedure better then having a function in my program that runs the same SQL requests? Is there a difference between what you would put in the code and a stored procedure?

Anonymous, meet google, a good friend of mine. He can teach you many things, one of which is that this question has been answered thousands of times online, even several times here. A query like "compare stored procedures to dynamic sql" should bring up many relevant discussions.

Quinnum · 2005-08-20 Reply Admin

brazzy:

A well-designed DB access layer will have each SQL statement in exactly one place, easy to find, reuse and refactor. There isn't really anything that SPs gain you in terms of maintainability compared with that.

Indeed. It's great and theoretical and academic to keep claiming 'well-designed' this and 'well-designed' that - but in reality, now many things out there in the real world are well designed, especially after years of bolt-on modifications? Then you end up hacking something up to get it out the door because the customer is screaming and the stupid PMs are jumping on your neck.

Many people on this forum seem to forget the realities a lot of the time - the world isn't ideal, nor are the system we end up having to deal with and (*shudder*) actually end up contributing further to the 'big ball of mud'
.

brazzy · 2005-08-21 Reply Admin

Quinnum:

Indeed. It's great and theoretical and academic to keep claiming 'well-designed' this and 'well-designed' that - but in reality, now many things out there in the real world are well designed, especially after years of bolt-on modifications? Then you end up hacking something up to get it out the door because the customer is screaming and the stupid PMs are jumping on your neck.

There are projects teams that resist pressure to bolt on ad hoc modifications. Usually they are the ones that find it relatively easy to meet deadlines AND customer expectations, even after years of development. But that's not the point here.

My point is that, contrary to what some people here claim, stored procedures are not a magic bullet that will make the system more maintainable. A well-designed system has clear separation of concerns and no duplication of code, a badly-designed system has e.g. DB access code all over the place and lots of copy-pasted code, no matter whether you use stored procedures of SQL statements in the application code.

In a badly designed system with SQL statements, you have similar SQL code in many places and changing it is a pain, just like Jeff described. But in a badly designed system with stored procedures, you instead have many similar stored procedures called from different parts of the code and changing it is ALSO a pain, because you need to identify with SPs are affected, where they are called from, and how a change in the SP will affect the code that calls it.

brazzy · 2005-08-21 Reply Admin

foxyshadis:

Anonymous, meet google, a good friend of mine. He can teach you many things, one of which is that this question has been answered thousands of times online, even several times here. A query like "compare stored procedures to dynamic sql" should bring up many relevant discussions.

A STFW answer makes you look booth arrogant and stupid when A) the proposed search term is not necessarily obvious and B) google does in fact not give any relevant result on the first page for it - both of which are the case here.

foxyshadis · 2005-08-21 Reply Admin

brazzy:
foxyshadis:

Anonymous, meet google, a good friend of mine. He can teach you many things, one of which is that this question has been answered thousands of times online, even several times here. A query like "compare stored procedures to dynamic sql" should bring up many relevant discussions.

A STFW answer makes you look booth arrogant and stupid when A) the proposed search term is not necessarily obvious and B) google does in fact not give any relevant result on the first page for it - both of which are the case here.

Nonetheless, it is a FAQ. A better query would have been "stored procedures vs sql" (no quotes) which gives many good answers on the first page. And that was just the first one that popped into my head without using 'dynamic'. There's many debates and facts around this, and boiling it down to a couple of peoples' opinions on Daily WTF won't do it justice. If you want to know about different databases (most hits are SQL Server), just add them to the query.

Quinnum · 2005-08-21 Reply Admin

brazzy:

There are projects teams that resist pressure to bolt on ad hoc modifications. Usually they are the ones that find it relatively easy to meet deadlines AND customer expectations, even after years of development. But that's not the point here.

My point is that, contrary to what some people here claim, stored procedures are not a magic bullet that will make the system more maintainable. A well-designed system has clear separation of concerns and no duplication of code, a badly-designed system has e.g. DB access code all over the place and lots of copy-pasted code, no matter whether you use stored procedures of SQL statements in the application code.

In a badly designed system with SQL statements, you have similar SQL code in many places and changing it is a pain, just like Jeff described. But in a badly designed system with stored procedures, you instead have many similar stored procedures called from different parts of the code and changing it is ALSO a pain, because you need to identify with SPs are affected, where they are called from, and how a change in the SP will affect the code that calls it.

Oh, I fully agree. It was just the 'well-designed' bit that I was commenting on - especially when you end up inheriting something that was nasty to begin with. I just wonder how many production systems out there are actually well designed, especially stuff that was written 10+ years ago and is still in use after over a decade of band-aids and incompetant project management.

I've found that the pressure from management who neither know nor care about design, and just want the thing out the door asap can be incredible - especially when you get pulled into the CEO's office and they start dangling contract obligations and penalty clauses for an unrealistic deadline (even though it's their fault for agreeing to such a stupid contract with the client in the first place) and basically say "it's gotta be done or we end up losing money". [:(] Forcing me to compromise my principles time and time again.

When I was getting the occasional breathing space, I was quietly refactoring especially bad parts of the system. Now that they've shipped development to India (thanks to the small company being eaten by a large company), that's not gonna happen anymore. Ah well, time for me to move on anyway, I think seven years at one place is long enough....

drjava · 2005-08-22 Reply Admin

richleick:

Well, duh! He is obviously still missing a lot:

<FONT color=#000099>CREATE PROCEDURE</FONT> [SADynamicTPSBuilder]
(
  @SELECTS <FONT color=#000099>VARCHAR</FONT>(8000),
  @JOINS <FONT color=#000099>VARCHAR</FONT>(8000)
  @WHERES <FONT color=#000099>VARCHAR</FONT>(8000)
  @ORDERS <FONT color=#000099>VARCHAR</FONT>(8000)
) <FONT color=#000099>AS
BEGIN

  SET NOCOUNT ON
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  EXEC</FONT> (@SELECTS + @JOINS + @WHERES + @ORDERS)
  
  <FONT color=#000099>RETURN</FONT>(-1)

<FONT color=#000099>END</FONT>

I mean seriously, what's the point if I can't specify my where and order by clause.

"I sense the sarcasm."
"Good because I'm laying it on pretty thick."

This is a completely, utterly and totally unfunny continuation of the original wtf.

christoofar · 2005-08-22 Reply Admin

You laugh, but this technique is actually quite valuable... I've used it quite often to created "canned queries," typically a user-interface that includes some sort of drop down or other control and a Developer Express grid (www.devexpress.com). When requirements come in to create a "view" say...

Can you make XYZ show the FOO data with the BAR figures, but only for the last 3 weeks if.... [blah blah more stupid requirements follow]

It's so much easier to do this by offering a canned query feature, dynamically build the viewing grid off of the resultset columns, and store the SQL in a report definition table. When requirements change, you can just make a quick data change, no recompilation/deployment is necessary, and everything remains centralized.

Of course, for large systems this solution is NOT ideal; you should be investing in a data warehouse and using report scheduling software instead.

2005-08-22 Reply Admin

Binary adherence to an all or nothing policy regarding stored procedures is silly. There are realistic alternatives to SPs that acheive some of the same benefits and add others. There are also times when an SP is just the best way to do it.

The canonical debate, with good points on both sides begins here:

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

Don't make statements of blind faith for or against SPs without having at least read some of the counterpoints.

nasch · 2005-08-22 Reply Admin

bugsRus:
Mung Kee:
Sean Connery:
johnl:

...and how it would affect our planned buctracking/knowledge base system if we allowed free text searches.

Oh man. One typo away from butcracking.

Sean Connery: "I'll take Anal Bum Cover for 7000."
Trebek: "That's An Album Cover."

Sean Connery: "I'll take Penis Mighter for 1000."
Trebek: "That's Pen Is Mighter."

Sean Connery: "I'll take The Rapists for 200." Alex Trebek: "That's Therapists."

2005-08-22 Reply Admin

Anonymous:
evnafets:
Obviously the WTF is that he included no exception handling in the stored procedure.
This code needs to be added to the end:
EXCEPTION
  WHEN others 
  THEN
    NULL;
[8-)]
Hmm, which RDBMS is that for then?

It's from Oracle.

2005-08-22 Reply Admin

Anonymous:
evnafets:
Obviously the WTF is that he included no exception handling in the stored procedure.
This code needs to be added to the end:
EXCEPTION
  WHEN others 
  THEN
    NULL;
[8-)]
Hmm, which RDBMS is that for then?

It's from Oracle. Last post went into a formatting spasm, hopefully this one works.

2005-08-22 Reply Admin

Anonymous:
I'm nieve and curious. Can someone explain why is a stored procedure better then having a function in my program that runs the same SQL requests? Is there a difference between what you would put in the code and a stored procedure?

As posted, there is no difference. If the stored procedure does not first verify it's inputs, then it is useless. Might as well just use raw SQL.

The code review for all new stored procedures ensures that they all properly verify the data provided. This would work. However, our HOPC (Highly OverPaidConsultant) seems not to get this.

Lastly, properly written stored procedures allow you to alter the database structure without touching the code. You just re-write the procedures to work with the new tables. Yet another reason why that junk needed to be canned.

2005-08-23 Reply Admin

I believe that's Oracle. Interesting debate. Rudy sent me over here.

Dynamic SQL in Application code?. Never

Dynamic SQL in Admin code? Almost Always.

xp_cmdshell inside sprocs. Sure, why not. What's the beef with this?

Access to the database. Only via sprocs. Java, .net, whatever for me is purely presentation layer. All business logic should reside with the data in the database.

<SHUDDER>Imagine, allowing developers to code logic...</SHUDDER>

Thanks for the link Rudy

2005-08-23 Reply Admin

"Access to the database. Only via sprocs. Java, .net, whatever for me is purely presentation layer. All business logic should reside with the data in the database. "

Yes. The DBMS is a logical inference machine. Why is it that we, in IT, continually repeat the mistakes of the past even though we have the solution (DBMS)? Why do other disciplines not have this problem? And yet we wail and moan at outsourcing -- when companies are doing so partly because of decreased confidence in our own abilities. We reap what we sow.

bugmenot · 2005-08-24 Reply Admin

Mung Kee:
Alex Papadimoulis:
CREATE PROCEDURE [SADynamicTPSBuilder]
(
 @SELECTS VARCHAR(8000),
 @JOINS VARCHAR(8000)
) AS
BEGIN

 SET NOCOUNT ON
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 EXEC (@SELECTS + @JOINS)
 
 RETURN(-1)

END
The only thing he did was allow a 16k character sql statement instead of 8k. He/she must be one of those dot com throwbacks who got into it for the money but shouldn't even be out of the house without their hickey helmet...let alone programming. I thought they all went back to their hr/marketing/landscaping/etc. jobs by now.

Nah, MS FrontPage and MS Access developer wannabees are alive and well and living in a neighborhood near you. The only way to get rid of them is to break out your Slim Whitman albums and play 'em full blast.

2005-09-05 Reply Admin

Maurits:
But it ALSO applies to dynamically built SQL within a stored procedure. Any time you use EXEC or sp_executesql or any of their cousins, you have to stop and consider the possibility of injection.

If you are using sp_executesql with parameters (EXEC doesn't let you do it), you have nothing to worry about.

2005-10-01 Reply Admin

OMG! I wonder how people out there realize that if they dumped ALL of their tests, retests, and tests of tests that they could completely rewrite their application from scratch 10 times over. Furthermore, just like sports, programmers get in a "zone" and create results in spurts - not necessarily when the scheduled/ alloted time happened to pass to complete a stage of the project.

Now, don't confuse my statement with design, rotten design leads to pathetic code. However, it is humerous to see how many confuse design with writing the code. Then, I can get to design strategies. The current educational and typical employment hierarchy leads one to believe a prgrammer should be educated on computer design and then should meet with "users" and map out what they desire. The problem is that "users' don't have a fricken clue what is truly possible and request the wrong solutions time and time again. A few rare programmers also understand the business (accounting, operations, marketing, taxes, etc.). These programmers are then trypically withheld from producing what is truly awesome.

My advice, if you are a guru programmer - WTF are you doing dealing with the BS in your organization? Most of you could rewrite the app you work on now in one to two years and it would be far superior.

Create your own and set yourself up. Forget these #%$#& boneheads that have no idea what is truly possible that you work for.

In other words, take a risk, spread your wings and take flight!

Sheesh!

2007-01-11 Reply Admin

And his hourly rate was? Holy crap, whats a luzer!

2008-09-12 Reply Admin

titltn21:
Man do I love SQL WTFs !!! Perhaps the next attempt will involve STEP 1: Inserting the SQL statement into some SQLLibrary table STEP 2: Present a stored proc that retrieves the SQL statement from SQLLibrary by a passed ID and executes the retrieved SQL string bye bye SQL Injection .. really ... CREATE PROCEDURE [DynamicTPSBuilder]( @SQLID INT) AS BEGIN SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @SQL VARCHAR(8000) --Get SQL SELECT @SQL = SQLString FROM SQLLibrary WHERE SQLID = @SQLID --Exec SQL EXEC (@SQL) RETURN(-1) END

I'm not happy to say I've once worked with a software that used a similar library.

SQL Injection Prevention 101

Leave a comment on “SQL Injection Prevention 101”