• (cs) in reply to Mr B
    Mr B:
    OzPeter:
    Except in the real world there are somethings you cannot choose nor can you change.

    Of course you can change it. Either you re-write the schema, or you have a shoddy proprietary schema trigger-linked into a sensible schema, of your design, which you can report on.

    Sheesh, you lot are such defeatists at times!

    :)

    Ah, yes, the magical "you can do anything because its a computer" line of thought.

    First of all let me introduce you to an interesting term - ROI

    Secondly the "shoddy schema" is auto generated by third party program that talks directly to the SQL server. This can also happen dynamically. So any trigger scheme you think of needs to compensate for that behaviour. At which point you have created a maintenance nightmare by having custom code that has to be slaved to the behaviour of that third party program.

    Oh - and all of this happens in an environment with very little .. actually no .. real IT support.

    You youngin's don't seem to understand the concept of pragmatism.

  • t-bone (lost password an in a hurry) (unregistered) in reply to Tim
    Tim:
    I realise this isn't a database forum but does anyone have a feel for the quantitative performance improvement from using stored procedures? my asp.net application uses direct SQL because this seemed to be the best way to support 4 different database platforms without a lot of re-coding and re-testing

    we mainly use simple sql statements with primary keys and indexed lookups, and we have never had performance problems in a real life situation.

    • Compiled execution plan for stored procedures, but then again that could work against you, especially when using dynamic sql.
    • bytes going over the wire, "sp_getsomething" is a lot less to send to your sql server than a query half a page long.

    That's about it.

  • Lee K-T (unregistered) in reply to aysunmoon
    aysunmoon:
    This has grown to concern me more than a little bit over the years -- Why is our standard response to incompetence within our workplaces an automatic "Run, Forrest! Run!"? Where are the efforts to actually *do something* about the rampant unprofessionalism within our trade? Other professions seem to have found ways to enforce at least some semblance of discipline upon their respective practitioners, so why do we continue to throw our hands up and say "What are you gonna do?" when faced with the messes left behind by the countless clueless hacks infesting our ranks? Is it that we are having trouble letting go of the hobbyist spirit that drew so many of us to this career to begin with? (And yes, I'm aware of some small irony in asking this on a site founded on our twisted fascination in watching it all go so wrong). So really... WTF?

    Well basically bad developers are way better politicians than good developers. Survival Instinct, I suppose.

  • tekiegreg (unregistered) in reply to SoonerMatt

    Yes because you're converting back to string and executing and not leaving the params alone, you got 2 char(50) blocks with which to arbitrarily execute your own statement, happy hacking!

  • proster (unregistered) in reply to OzPeter

    you can use dynamic sql to CREATE SYNONYM to avoid dynamic sqling to change database / table name : http://msdn.microsoft.com/en-us/library/ms177544.aspx

    still needs a bit of dynamic but far less than before.

  • ClaudeSuck.de (unregistered) in reply to Madox
    Madox:
    James L:
    I'll correct myself.

    This is the only way to get SPs to work if they use Temp Tables

    What kind of crack are you smoking? Since when can't you use a temp table within a regular sproc?

    What are you on, Sybase 6?

    Please send me teh crackz

  • c-sharper (unregistered) in reply to Madoxx
    Madoxx:
    For the uninitiated, the db calls go into the Data Access Layer. That's assuming a standard 3 tier app structure (presentation, business logic, data access). So for the love of the spaghetti monster, stop calling sprocs in your front end.

    Lolz, agreed. I was referring more to front end as in the properly separated non-DB portion of your application

  • ClaudeSuck.de (unregistered) in reply to Smash King
    Smash King:
    ClaudeSuck.de:
    Smash King:
    If exec wasn't bad enough...

    1- As A Nonny Mouse noticed, the coder used char when varchar would make much more sense. 2- What's with Ltrim and Rtrim? I don't know all RDBMS out there but I'm pretty sure the most widely used accept Trim or a similar function. And probably the idiot had to trim the string because of reason 1 above. 3- Why would you join the tables UserRelationship and Batch and not use them in your where or select clauses? (Yes I know it is possible to filter this way, but someone as clueless as this coder wouldn't know it)

    Of course, exec still is their worst problem... no, wait. Forget that. Their worst problem is a total cluelessness. The rest is consequence.

    Hmmm, no clue? Point 1) See before last paragraph. Point 2 is wrong because SQL Server does not have a TRIM function, no, no. That's pure old SQL Server. Point 3) you still have to use the table names in your FROM clause (and for some DBs you even need to put the WHERE fields in the SELECT clause first). That's pure old SQL.

    I doubt that it's the front-end developer's fault. It's more the database designer who went gaga.

    Hmmm, who has clues and who has not?

    1) Where? In the CodeSOD? I have no idea what you meant here. 2) Ok, my mistake. SQL Server doesn't have Trim. But as someone above noted, trimming a datetime field is a WTF in itself and provides another detail as to why it's not absurd to say this guy is clueless. 3) You did not understand my point at all. Please go back and re-check. Try again later.

    You're probably right that the DB schema must be a mess, but it's no excuse for this set of stupidities.

    1. I doubt that it's the front-end developer's fault. It's more the database designer who went gaga.
    2. Indeed. TL;DR. Since when can one use table names in the SELECT clause? Using subqueries? Hmm
  • c-sharper (unregistered) in reply to James L
    James L:
    Joon:

    That's some good trolling. Well done!

    Let's see how many people fall for it and start saying "But to the front end this proc would look no different no matter how the back-end does it..."

    Examples of this WTF used in real life, too:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/70af8b07-8e1a-44c7-9f27-6ec6dc0900f6/

    Hehe, I stand corrected. But I suppose that is your just desserts for using data-aware controls (Got in some of my own trolling there...)

  • Steve (unregistered)

    I have a better way (I am a different Steve):

    Create Procedure [dbo].[Authenticate] @strSQL char(2000)

    AS

    Exec (@strSQL)

  • ClaudeSuck.de (unregistered) in reply to pbo
    pbo:
    ClaudeSuck.de:
    After some 10 years with sps [...] let me say tables above some 100.000 lines.

    Shouldn't you be calling those things 'tuples' after ten years?

    Usually, each row is different (should be at least). I believe 'tuples' are multiple occurencies of equal things. Hence, I call it row, record, line and so on. BTW I haven't yet reached the programming Nirwana. So I am still talking about mundane and not so much about abstract things.

  • ClaudeSuck.de (unregistered) in reply to Mr B
    Mr B:
    dpm:
    Mr B:
    OzPeter:
    Either I write an SP with dynamic SQL or I write 15 separate SPs.
    Or you use a better schema.
    What a wonderful dream, that the person responsible for writing the SPs is the same person with the authority to change the schema.

    Not in my world, though.

    Get a new world then, in my world the schema and the queries are treated as a single unit, under the control of the database scrum, which has representatives from ops, developers and dbas.

    Lack of organisational structure is the first wtf, compounded by simply going along with it for the sake of an easy life. Typical wtf'er really, complaining about the state of things and yet unwilling to do anything about it.

    :)

    Ever had a PHB? How many well orgainzed companies can you cite here with proper database setup, code review, testing, staging, maintenance, documentation, and all the rest that makes up a structured dev/test/prod environment. Haven't seen a single one yet. However, some are getting close or at least have tried to.

  • ClaudeSuck.de (unregistered) in reply to threecheese
    threecheese:
    OzPeter:
    Single User:
    I never understood why exec was even legal in stored procedures.

    You can't dynamically specify table names in a SP, thus it makes it harder to create a generic SP that might have to extract data from different tables.

    I am dealing with this situation with multiple data logging tables generated by similar pieces of equipment.

    Either I write an SP with dynamic SQL or I write 15 separate SPs.

    IMO you could use a trigger (if tsql) to write that data to a common table.

    Tadaaa. We have found the winner of today's WTF contest with the most WTFy solution.

  • (cs) in reply to redbeard0x0a
    redbeard0x0a:
    Smash King:
    If exec wasn't bad enough... 2- What's with Ltrim and Rtrim? I don't know all RDBMS out there but I'm pretty sure the most widely used accept Trim or a similar function. And probably the idiot had to trim the string because of reason 1 above.

    The wonderful world of Microsoft SQL Server is where you find only RTRIM and LTRIM with no TRIM in sight. Don't forget they didn't have DATE or TIME datatypes until SQL Server 2008, you had to make due with DATETIME...

    Ooo! I know this one!

    Date = convert(char(10), [date field], 101) Time = convert(char(12), [date field], 114)

    I have to use those things all the freakin' time.

  • Not THAT Alex (unregistered) in reply to aysunmoon
    aysunmoon:
    This has grown to concern me more than a little bit over the years -- Why is our standard response to incompetence within our workplaces an automatic "Run, Forrest! Run!"? Where are the efforts to actually *do something* about the rampant unprofessionalism within our trade? Other professions seem to have found ways to enforce at least some semblance of discipline upon their respective practitioners, so why do we continue to throw our hands up and say "What are you gonna do?" when faced with the messes left behind by the countless clueless hacks infesting our ranks? Is it that we are having trouble letting go of the hobbyist spirit that drew so many of us to this career to begin with? (And yes, I'm aware of some small irony in asking this on a site founded on our twisted fascination in watching it all go so wrong). So really... WTF?

    Sometimes the only way of solving anything requires ignoring the organizational structure and bypassing management decisions, since often the management doesn't agree with the suggested changes. It may end up harming one's carreer.

  • Madoxx (unregistered) in reply to Smash King
    Smash King:
    2- What's with Ltrim and Rtrim? I don't know all RDBMS out there but I'm pretty sure the most widely used accept Trim or a similar function. And probably the idiot had to trim the string because of reason 1 above.

    For the non-DBAs out there, SQL Server 2005 doesn't have a Trim() function; only RTrim() and LTrim(). So every time you deal with a moron who used char when he should've used varchar, you have to fugly-up your code like this:

    RTrim(LTrim(field_name))

  • ClaudeSuck.de (unregistered) in reply to t-bone (lost password an in a hurry)
    t-bone (lost password an in a hurry):
    • Compiled execution plan for stored procedures, but then again that could work against you, especially when using dynamic sql.

    Can also happen for static queries when statistics are not up to date

  • (cs) in reply to ClaudeSuck.de
    ClaudeSuck.de:
    Mr B:
    dpm:
    Mr B:
    OzPeter:
    Either I write an SP with dynamic SQL or I write 15 separate SPs.
    Or you use a better schema.
    What a wonderful dream, that the person responsible for writing the SPs is the same person with the authority to change the schema.

    Not in my world, though.

    Get a new world then, in my world the schema and the queries are treated as a single unit, under the control of the database scrum, which has representatives from ops, developers and dbas.

    Lack of organisational structure is the first wtf, compounded by simply going along with it for the sake of an easy life. Typical wtf'er really, complaining about the state of things and yet unwilling to do anything about it.

    :)

    Ever had a PHB? How many well orgainzed companies can you cite here with proper database setup, code review, testing, staging, maintenance, documentation, and all the rest that makes up a structured dev/test/prod environment. Haven't seen a single one yet. However, some are getting close or at least have tried to.

    All the ones I've ever worked for.

    Don't misunderstand me, it's never been perfect, it never will be - but there's certain areas that can be mitigated against - and the worst thing you can EVER do is compound one wtf with another one.

  • Madoxx (unregistered) in reply to ClaudeSuck.de
    ClaudeSuck.de:
    OzPeter:
    Single User:
    I never understood why exec was even legal in stored procedures.

    You can't dynamically specify table names in a SP, thus it makes it harder to create a generic SP that might have to extract data from different tables.

    I am dealing with this situation with multiple data logging tables generated by similar pieces of equipment.

    Either I write an SP with dynamic SQL or I write 15 separate SPs.

    In case of an authentication (that's what the sp does) it doesn't make much sense to query different tables for different logons. I'd say there is a terrible design problem somewhere.

    I think you mis-read him. He's not using different tables based on the user's logon. He's using different logging tables based on a parameter. It could be that for each piece of logging equipment, it uses it's own table (as an example).

  • Bytemaster (unregistered) in reply to Tim

    It depends on the Database server / version you are using.

    For SQL Server, prior to SQL 2000 it made a big difference because your queries would always get recompiled without it.

    Starting with SQL 2000, there is a stored procedure that can be called that will let you re-use a query plan. If you use parametrized queries in ADO.NET, it takes care of this for you.

    Starting with SQL 2005, it is still best to use parametrized queries or stored procedures, but it also tries to auto-parametrize your queries so that it can use plans again. There is also a database option to make it try harder to auto-parametrize your queries. This makes the stored procedure speed improvement less noticeable if it is successful at the auto-parametrization.

    Now in .NET we have LINQ to SQL, which is also available for other platforms such as DB2. Using this optimizes the queries for you, and uses parametrized queries automatically. It even uses other optimizations which we as programmers usually skip because of time or just cluttering up the code so its insert and update statements are faster than normal.

  • Madoxx (unregistered) in reply to pbo
    pbo:
    ClaudeSuck.de:
    After some 10 years with sps [...] let me say tables above some 100.000 lines.

    Shouldn't you be calling those things 'tuples' after ten years?

    I did that right out of college. Suffice it to say, they aren't called "tuples" in the real world. Only academia (with all the respect due to Ted Codd).

  • Madoxx (unregistered) in reply to redbeard0x0a
    redbeard0x0a:
    Smash King:
    If exec wasn't bad enough... 2- What's with Ltrim and Rtrim? I don't know all RDBMS out there but I'm pretty sure the most widely used accept Trim or a similar function. And probably the idiot had to trim the string because of reason 1 above.

    The wonderful world of Microsoft SQL Server is where you find only RTRIM and LTRIM with no TRIM in sight. Don't forget they didn't have DATE or TIME datatypes until SQL Server 2008, you had to make due with DATETIME...

    Ahhhh, yes. But Microsoft's own documentation says something assinine to the effect of "If you don't like the lack of Trim(), then make your own SQL Server function. "

    Thanks Microsoft.

  • (cs) in reply to proster
    proster:
    you can use dynamic sql to CREATE SYNONYM to avoid dynamic sqling to change database / table name : http://msdn.microsoft.com/en-us/library/ms177544.aspx

    still needs a bit of dynamic but far less than before.

    I mostly work with SQL 2000 in practice, but you can still limit the dynamic portion to something like "create view x as select * from y.z" (I use this in a bunch of one-off scripts to migrate from a gimpy product to a non-gimpy one, both of which our company works with)

  • wombat (unregistered)

    Ignoring the issue of dynamic SQL inside stored procs for a moment, a major reason for requiring all access to be through stored procs is security. You can grant execute permissions to stored procs that select, insert, update, or delete data from tables that the user has no rights to.

  • Madoxx (unregistered) in reply to wombat
    wombat:
    Ignoring the issue of dynamic SQL inside stored procs for a moment, a major reason for requiring all access to be through stored procs is security. You can grant execute permissions to stored procs that select, insert, update, or delete data from tables that the user has no rights to.

    ding ding ding

    Finally someone gets it. Been waiting for this to get mentioned. It's straight out of any Intro to DBs book (profession books, not academic).

  • Jay (unregistered)

    This sounds to me like a classic example of:

    1. We have a problem.
    2. We can't think of a real solution.
    3. But we have to do SOMETHING.
    4. So let's do something that wastes a bunch of time and/or money while doing nothing to solve the real problem.

    Sure, WELL-WRITTEN stored procedures would not be subject to SQL injection. But well-written queries embedded in the code would not be subject to SQL injection either.

    Of course this phenomenon is not limited to the IT world. You often see it in politics. Personally, I think about 90% of our laws fall into this category.

  • im on my own here (unregistered) in reply to Madoxx
    Madoxx:
    ClaudeSuck.de:
    OzPeter:
    Single User:
    I never understood why exec was even legal in stored procedures.

    You can't dynamically specify table names in a SP, thus it makes it harder to create a generic SP that might have to extract data from different tables.

    I am dealing with this situation with multiple data logging tables generated by similar pieces of equipment.

    Either I write an SP with dynamic SQL or I write 15 separate SPs.

    In case of an authentication (that's what the sp does) it doesn't make much sense to query different tables for different logons. I'd say there is a terrible design problem somewhere.

    I think you mis-read him. He's not using different tables based on the user's logon. He's using different logging tables based on a parameter. It could be that for each piece of logging equipment, it uses it's own table (as an example).

    uh, not entirely sure if I've missed something, but whats this about different tables? he uses the parameter to look at a different DATABASE, not a different table. i'll give a shiny £1 to anyone who can come up with a better way of doing it (and not just a half assed explanation, or something that would involve restructuring the entire application)

  • Americium (unregistered) in reply to Single User
    Single User:
    I never understood why exec was even legal in stored procedures.

    CAPTCHA: autocompleted, yet again.

    PREPARE/EXECUTE is useful anywhere that a custom WHERE clause might occur. In order to allow optional terms, the procedural code has to choose to add it or not.

    Take ...WHERE NAME='BOB';

    Add some term ...WHERE NAME='BOB' ...AND NOT(JOB='MANAGER');

    A massive WHERE to handle all the cases can create code that's harder to maintain, like the CASE on a numeric code below.

    ...WHERE NAME='BOB' AND CASE 1 WHEN 1 THEN NOT(JOB='MANAGER') WHEN 2 THEN (JOB='MANAGER') ELSE 1=1 END;

  • iMalc (unregistered) in reply to redbeard0x0a
    redbeard0x0a:
    Smash King:
    If exec wasn't bad enough... 2- What's with Ltrim and Rtrim? I don't know all RDBMS out there but I'm pretty sure the most widely used accept Trim or a similar function. And probably the idiot had to trim the string because of reason 1 above.

    The wonderful world of Microsoft SQL Server is where you find only RTRIM and LTRIM with no TRIM in sight. Don't forget they didn't have DATE or TIME datatypes until SQL Server 2008, you had to make due with DATETIME...

    Oh I'm painfully aware of that one! Too bad we still have to support running our software on either version.

  • eff Five (unregistered)

    So the WTF is

    This line

    Exec (@strSQL)

    Should have been

    SET @ParmDefinition = N'@StartDate datetime, @EndDate datetime, @UserID numeric(18,0), @Password char(50), @DatabaseName char(50) '

    EXECUTE sp_executesql @SQLString, @ParmDefinition, @StartDate , @EndDate , @UserID , @Password , @DatabaseName

    Woo hoo

  • (cs) in reply to Protector one
    Protector one:
    Thanks for that. Exercises for the reader piss me off to no (all?) extent.

    Yes. The site covers a multitude of languages, and only a handful of readers will be proficient in all those covered. "Exercise for the reader" is a cop-out for someone who can't write a decent explanation. And there's the irony: the OP might have been proficient in SQL, but not in English.

    Idiot.

  • (cs) in reply to Madoxx
    Madoxx:
    Ahhhh, yes. But Microsoft's own documentation says something assinine to the effect of "If you don't like the lack of Trim(), then make your own SQL Server function. "

    Thanks Microsoft.

    Why not just "make your own SQL Server"?

  • (cs) in reply to Jay
    Jay:
    But well-written queries embedded in the code would not be subject to SQL injection either.
    i think you're deluding yourself. I find it difficult to believe your statement — unless you sanitize your input at the front end. Someone will find a way to inject SQL into your code regardless of how well written. Take the example. We all know it could be made much better (functional, even!), but you couldn't keep it dynamic without risking injection. If you think you could, I'd really like to see it. (sp_executesql doesn't count as that doesn't exist on other platforms that I've heard of).
  • nick (unregistered)

    I've never written a stored procedure before, but it seems easy. Is the below version safe from SQL injections? It doesn't use Exec() or string concatenation. I think it's right, except I'm not sure "ltrim(rtrim(@DatabaseName)).dbo.Users" is kosher. If not, how would you write that part?

    Create Procedure [dbo].[Authenticate] 
      @StartDate datetime, 
      @EndDate datetime, 
      @UserID numeric(18,0),
      @Password char(50),
      @DatabaseName char(50)
    
      AS
    
      Select count(c.ID) as Count, sum(sc.Total) as Users
      from Users sc inner join UserRelationship pr 
        on pr.PartyIDChild = sc.OrganizationID 
        inner join Batch b on b.BatchID = sc.BatchID 
        inner join ltrim(rtrim(@DatabaseName)).dbo.Users c 
        on sc.UserID= c.ID 
      where b.SentDate between ltrim(rtrim(@StartDate)) 
        and ltrim(rtrim(@EndDate))
        and c.UserID  = ltrim(rtrim(str(@UserID))) 
        and c.Password = ltrim(rtrim(str(@Password))) 
    
  • (cs) in reply to nick
    nick:
    I've never written a stored procedure before, but it seems easy. Is the below version safe from SQL injections? It doesn't use Exec() or string concatenation. I think it's right, except I'm not sure "ltrim(rtrim(@DatabaseName)).dbo.Users" is kosher. If not, how would you write that part?
    Your hunch is correct that using @Database name is not going to work. That's why it has to be either dynamic SQL or dealt with other ways. One would be to not name the database and store the proc in that database and exec the proc from inside that used database (it defaults to the database in use if not explicit). A bit more WTFy would be to switch on the database name to the same code with the different names enumerated (if a short list).

    Also, as was mentioned elsewhere, there's no logic to trimming spaces off of dates nor is there a point to joining to tables that aren't used in the select or where clauses (unless you want to be sure they are there but then that's more of a referential integrity issue and not something that is not normally checked via proc but via triggers or other means). The latter is difficult to discern without better knowledge of the ERD.

    If you're not trying to avoid SQL injection, dynamic SQL can be very effective in certain situations but those typically are rare.

  • Muppet (unregistered) in reply to nick

    Nope. You can't do;

    ltrim(rtrim(@DatabaseName)).dbo.Users

    and don't put ltrim rtrim inline with your SQL, do it within the procedure but before it, so;

    SELECT @UserID = ltrim(rtrim(@UserID))

    • you can't ltrim rtrim a date !

    And I always do;

    set nocount on set dateformat dmy (or whatever you want)

    STUFF

    set nocount off

    in my procedures as otherwise you'll get rowcounts out which confuses the hell out of .NET code.

    Now, one massive downside of using LinQ and any of the MS "database interface" tool sets / apis is that it needs for the resultant data to be returned in a consistant format.

    So if you have a procedure which can do multiple things and return potentially differently sized / multiple datasets depending on the input criteria / data then all the pre-compiled stubs and other clever MS'y stuff don't work.

    Our latest app uses the "only give the app permissions to execute procedures" and the permission is compounded by requiring a valid ticket which is given out when logging in.

    Oh and using GUID's for ID values... fantastic.

  • (cs) in reply to Muppet

    TRWTF is the quote mishmash we're expected to believe was really SQL.

    I hope there was some way to enforce that passwords began and ended with a single quote.

  • Jamie Nordmeyer (unregistered)

    Um. Ouch. That's really all I can say. I'd have left too.

  • JayC (unregistered) in reply to Madox

    He probably should have replied to himself instead of making a second message.

    In ASP.NET, we have this concept of connection pooling. Normally, a temporary table (there are two types, but I'm only talking of one of them) would persist until the connection closes. But with connection pooling, when a connection is "closed", it isn't necessarily closed in the TCP/IP sense of a closed clonnection. It may be recycled. Therefore the temp table isn't so temporary

    But you can utilize temp tables, and they be temporary, by using the string version of exec, as in exec ( 'create table #foo( i int);' + 'insert into #foo values (1);' + 'insert into #foo values (10);'+ ' select i from #foo')

    After the exec, #foo no longer exists.

  • Bob (unregistered) in reply to OzPeter
    OzPeter:
    You youngin's don't seem to understand the concept of pragmatism.
    Pragmatisim: noun, means "cry like a baby because 15 correct and safe stored procedures are so hard to write when instead you can have one stored procedure that opens you up to SQL injection attacks".

    Have I got it right, grandpa? Do I get a lollypop now?

  • (cs) in reply to Americium
    Americium:
    Single User:
    I never understood why exec was even legal in stored procedures.

    CAPTCHA: autocompleted, yet again.

    PREPARE/EXECUTE is useful anywhere that a custom WHERE clause might occur. In order to allow optional terms, the procedural code has to choose to add it or not.

    Take ...WHERE NAME='BOB';

    Add some term ...WHERE NAME='BOB' ...AND NOT(JOB='MANAGER');

    A massive WHERE to handle all the cases can create code that's harder to maintain, like the CASE on a numeric code below.

    ...WHERE NAME='BOB' AND CASE 1 WHEN 1 THEN NOT(JOB='MANAGER') WHEN 2 THEN (JOB='MANAGER') ELSE 1=1 END;

    In simpler cases, this sort of thing can be improved along the lines of

    WHERE NAME='BOB' AND JOB=COALESCE(@JOB,JOB)

    (i.e. if @JOB is null, then it degenerates to a 1=1 type condition)

  • real-modo (unregistered) in reply to OzPeter
    OzPeter:
    Single User:
    I never understood why exec was even legal in stored procedures.

    You can't dynamically specify table names in a SP, thus it makes it harder to create a generic SP that might have to extract data from different tables.

    I am dealing with this situation with multiple data logging tables generated by similar pieces of equipment.

    Either I write an SP with dynamic SQL or I write 15 separate SPs.

    Uhhh... create a (union) view? The BASE_TABLE_NAME can be a column in the view.

    A bit faster than dynamic SQL, IIRC.

  • Grandpa (unregistered) in reply to Bob
    Bob:
    OzPeter:
    You youngin's don't seem to understand the concept of pragmatism.
    Pragmatisim: noun, means "cry like a baby because 15 correct and safe stored procedures are so hard to write when instead you can have one stored procedure that opens you up to SQL injection attacks".

    Have I got it right, grandpa? Do I get a lollypop now?

    OK, so 15 aren't too bad, but what if it's 150? or 1500? and they all need maintenance as well? and what if you have 10 different sprocs that need to do this sort of thing? then you end up creating 15000 sprocs instead of 10. i know what makes more sense to me. particularly as in this situation it is unlikely user input would be sent for that param anyway

  • (cs)

    It's surprising that noone has fixed this yet. Here's my take:

    CREATE PROCEDURE [dbo].[Authenticate]     @StartDate DATETIME,     @EndDate DATETIME,     @UserID NUMERIC(18,0),     @Password CHAR(50),     @DatabaseName CHAR(50)

        AS

    DECLARE @strSQL NVARCHAR(2000)

    SET @strSQL =
        N'SELECT COUNT(c.ID) AS Count, SUM(sc.Total) AS Users     FROM Users sc     INNER JOIN UserRelationship pr on pr.PartyIDChild = sc.OrganizationID     INNER JOIN Batch b ON b.BatchID = sc.BatchID     INNER JOIN ' + QUOTENAME(LTRIM(RTRIM(@DatabaseName))) + N'.dbo.Users c on sc.UserID= c.ID     WHERE b.SentDate BETWEEN @StartDate AND @EndDate         AND c.UserID = LTRIM(RTRIM(@UserID)) AND c.Password = LTRIM(RTRIM(@Password))'

    EXEC sp_executesql @strSQL, N'     @StartDate DATETIME,     @EndDate DATETIME,     @UserID NUMERIC(18,0),     @Password CHAR(50)',     @StartDate,     @EndDate,     @UserID,     @Password     

  • Georgem (unregistered) in reply to Alan
    Alan:
    I never understood Microsofts insistence on providing ways to tie the front-end directly to the database.

    Write some code and use Object Data Binding instead

    MVC was just something that happened to other people, presumably

  • ClaudeSuck.de (unregistered) in reply to Grandpa
    Grandpa:
    Bob:
    OzPeter:
    You youngin's don't seem to understand the concept of pragmatism.
    Pragmatisim: noun, means "cry like a baby because 15 correct and safe stored procedures are so hard to write when instead you can have one stored procedure that opens you up to SQL injection attacks".

    Have I got it right, grandpa? Do I get a lollypop now?

    OK, so 15 aren't too bad, but what if it's 150? or 1500? and they all need maintenance as well? and what if you have 10 different sprocs that need to do this sort of thing? then you end up creating 15000 sprocs instead of 10. i know what makes more sense to me. particularly as in this situation it is unlikely user input would be sent for that param anyway

    Real programmers don't need no stinkin' SQL. They store information on file cards.

  • Elvis (unregistered)

    It seems like they should have sorted out the problem with users being in different databases rather than come up with this abortion.

    As has been pointed out, all of the SQL Injection none of the Optimisation.

    My guess is that there was some sort of office politics which required the developer to come up with this nasty hack rather than fix the database issue. A sad but common story.

  • nick (unregistered) in reply to SlyEcho
    SlyEcho:
    It's surprising that noone has fixed this yet. Here's my take:

    Aren't you still concatenating strings and sending them to EXEC? I thought that was the source of the injection vulnerability.

  • Devil's Advocate (unregistered) in reply to aysunmoon
    aysunmoon:
    This has grown to concern me more than a little bit over the years -- Why is our standard response to incompetence within our workplaces an automatic "Run, Forrest! Run!"? Where are the efforts to actually *do something* about the rampant unprofessionalism within our trade? Other professions seem to have found ways to enforce at least some semblance of discipline upon their respective practitioners, so why do we continue to throw our hands up and say "What are you gonna do?" when faced with the messes left behind by the countless clueless hacks infesting our ranks? Is it that we are having trouble letting go of the hobbyist spirit that drew so many of us to this career to begin with? (And yes, I'm aware of some small irony in asking this on a site founded on our twisted fascination in watching it all go so wrong). So really... WTF?

    Because usually pointing it out will get you fired. In this case the code was written by a "senior developer" who has more clout than anybody else, so if you say "Hey this code is shit" then YOU will be the one fired.

    The sad part about our profession is that talent isn't rewarded, hackery and good BS skills are. I've seen a good developer let go without notice from a mediocre team because the good person was trying to encourage the right way to write software, but nobody else was interested in doing it the "right" way.

  • Infidel (unregistered) in reply to Single User
    Single User:
    I never understood why exec was even legal in stored procedures.
    Many reaons, one that has affected me recently is pivots with dynamic columns: http://www.sqlprof.com/blogs/sqldev/archive/2008/04/12/pivots-with-dynamic-columns-in-sql-server-2005-2008.aspx

Leave a comment on “All Pain, No Gain”

Log In or post as a guest

Replying to comment #:

« Return to Article