• (cs) in reply to coltonsk
    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.

  • (cs) in reply to Richard Nixon
    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...

  • (cs) in reply to tufty

    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.
  • (cs) in reply to coltonsk
    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.
  • (cs) in reply to cjs
    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. [;)]

  • (cs) in reply to AC

    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.

  • (cs) in reply to El Duderino
    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"

  • (cs) in reply to PhiloChronos

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

  • (cs) in reply to DZ-Jay

    --------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.

  • RubberDuckie (unregistered)

    Uh boy!!!

    [image]

  • (cs) in reply to WIldpeaks
    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}"
  • (cs) in reply to brazzy
    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.

  • tSQL (unregistered)

    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.

  • (cs) in reply to Mat&#237;as
    Anonymous:
    Anonymous:

    <font face="Courier New">

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

    </font><font face="Courier New"></font>

    <font face="Courier New">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.</font>


    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.
  • (cs) in reply to Mung Kee
    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."
  • RaolinDarksbane (unregistered) in reply to mm
    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.
  • RaolinDarksbane (unregistered) in reply to tökk
    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
  • (cs) in reply to bugsRus

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

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

  • (cs) in reply to cjs

    <font style="font-family: arial;" size="2">

    cjs:
    dubwai:
    </font>

    <font size="2">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.</font>

    <font style="font-family: arial;" size="2">


    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?!</font>

  • (cs) in reply to John Smallberries
    John Smallberries:
    <FONT style="FONT-FAMILY: arial" size=2>
    cjs:
    dubwai:
    </FONT>

    <FONT size=2>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.</FONT>

    <FONT style="FONT-FAMILY: arial" size=2>



    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?!</FONT>

    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.

  • Dwayne Hicks (unregistered)

    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?

  • (cs) in reply to dubwai
    dubwai:
    John Smallberries:
    <font style="font-family: arial;" size="2">
    cjs:
    dubwai:
    </font>

    <font size="2">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.</font>

    <font style="font-family: arial;" size="2">



    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?!</font>

    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 ....


  • (cs) in reply to Jeff S

    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  . 


  • (cs) in reply to Jeff S

    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.

  • (cs) in reply to Richard Nixon
    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.

  • (cs) in reply to Jeff S
    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.

  • pagh (unregistered) in reply to Mat&#237;as
    Anonymous:
    Anonymous:

    <FONT face="Courier New">

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

    </FONT><FONT face="Courier New"></FONT>

    <FONT face="Courier New">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.</FONT>


    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".

    <FONT face="Courier New">In MS SQL Server (which has an 8000 character limit on chars/varchars and 4000 on nchars/nvarchars), strings really are truncated. Example:</FONT>

    <FONT face="Courier New">declare @x varchar(8000)
    </FONT><FONT face="Courier New">set @x = 'really long string (~6000 characters)'
    select len(@x)
    select len(@x + @x)</FONT>

    <FONT face="Courier New">The first select returns ~6000, and the second returns 8000.</FONT>

    <FONT face="Courier New">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.</FONT>

  • JM (unregistered) in reply to Gah.

    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 :-)

     

     

  • Annomous Coward (unregistered) in reply to JM

    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?

  • Jiminy Jones (unregistered) in reply to Annomous Coward

    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.

  • (cs) in reply to Annomous Coward
    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.
  • (cs) in reply to brazzy
    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'
    .
  • (cs) in reply to Quinnum
    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.
  • (cs) in reply to foxyshadis
    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.
  • (cs) in reply to brazzy
    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.
  • (cs) in reply to brazzy

    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....

     

  • (cs) in reply to richleick
    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.

  • (cs) in reply to titltn21

    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.

  • What About Thad? (unregistered)

    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.

  • (cs) in reply to bugsRus
    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."

  • Purple People Eater (unregistered) in reply to Gah.
    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.

  • Purple People Eater (unregistered) in reply to Gah.
    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.

  • robert (unregistered) in reply to Annomous Coward
    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.
  • x002548 (unregistered) in reply to Gah.

    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

  • DBA (unregistered) in reply to x002548

    "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.


  • (cs) in reply to Mung Kee
    Mung Kee:
    Alex Papadimoulis:

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

    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    EXEC</FONT> (@SELECTS + @JOINS)

    <FONT color=#000099>RETURN</FONT>(-1)

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



    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.

  • Matthew (unregistered) in reply to Maurits

    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.

  • Just Lookn (unregistered) in reply to Matthew

    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!

     

     

  • WTF Jim (unregistered)

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

  • Joao Marcus (unregistered) in reply to titltn21
    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.

Leave a comment on “SQL Injection Prevention 101”

Log In or post as a guest

Replying to comment #:

« Return to Article