Re: why use the data command parameter collection

From: William Ryan eMVP (dotnetguru_at_comcast.nospam.net)
Date: 04/19/04

  • Next message: William Ryan eMVP: "Re: Updating Dataset" 
    Date: Mon, 19 Apr 2004 15:40:01 -0400

    Yikes! Although the first part is a matter of taste I guess, I never
    thought I'd imagine someone saying that concatenating strings is less
    cumbersome than using an iterative object oriented approach. If my command
    takes three parameters, logicially I think adding them to a collection and
    being able to retrieve them though iteration for example is much less
    cumbersome. Moreover, you could possibly run into problems with double
    quote characters or other such input by concatenating strings. Don't even
    get me started on the immutability of strings issue ;-).

    First, about the whole other collection.... every command object has a
    Parameters Collection. It may be empty or may have a ton of stuff, but it
    has a Paramaters collection. To that end, I don't see what "whole other
    collection " you are referring to

    Also the approach string concatenation leaves a lot to be desired (see
    doesn't cut it) if you are using column mappings for instance. How would
    you map those parameters to a datatable so that the Update/Insert/Delete
    command could automatically pick up the values on update? Using the table
    mappings and colum mappings is a big issue and not using them just so you
    can concatenate strings isn't really a good trade off. Also, string
    concatentaion is a bit clunky and if you forgot to add a singly quote or
    something, you could cause an error. Depending on how you pass in the
    values, you could open yourself up to Injection attacks or issues with
    people who's last name may be O'Ryan or the like. Of if you had text that
    had CarriageReturns in it, and that was to be stored in the db, you might
    get into trouble with string concatenation.

    Another issue is clarity and precision.

    If I used this:

    cmd.Parameters.Add("@param1", SqlDbType.VarChar, 10).Value = "NULL"
    cmd.Parameters.Add("@param2", SqlDbType.Int, 4).Value = 100
    cmd.Parameters.Add("@param3", SqlDbType.Varchar, 20).Value = 200

    I have complete control over what gets sent. Even if I validate at the
    control /UI level, I can make sure that nothing more than 20 characters long
    gets sent at @Param1for instance. This is critical to preventing Injection
    attacks and many other security threats b/c you can't do much damage if I
    keep the input you can enter to 6 characters for instance, it takes a few
    more to write ;DELETE * FROM SystObjects. Also, as a fellow programmer, I
    could see what the code is intended to do and understand it a whole lot
    better.

    I guess from a style perspective, to each his own. But there are many that
    would argue that string concatenation is really irksome and hard to read
    maintain, and not the least bit object oriented. There's no way to deal
    with it iteratively.....and you run the risk of introducing some serious
    security problems.

    HTH,

    Bill
    "Mad Scientist Jr" <usenet_daughter@yahoo.com> wrote in message
    news:7a93f3c4.0404191047.48d389c7@posting.google.com...
    > Could someone please explain the advantage of using the data command
    > parameter collection to send paramters to SQL, instead of simply
    > building a SQL string like this:
    >
    > sSQL = "exec mysproc @param1=[param1/], @param2=[param2/],
    > @param3=[param3/]"
    > sSQL = replace(sSQL, "[param1/], "NULL")
    > sSQL = replace(sSQL, "[param2/], "'" & sParam2 & "'")
    > sSQL = replace(sSQL, "[param3/], iParam3.toString)
    >
    > Isn't it a bit cumbersome to use a whole other collection, instead of
    > working with simple SQL strings?
    >
    > I know using a separate collection is probably the "right" way, but I
    > just would like a solid explanation of why.
    >
    > Thanks in advance...

  • Next message: William Ryan eMVP: "Re: Updating Dataset"

    Relevant Pages

    • Re: string concatenation optimizations [from python-dev Summary]
      ... Essentially what I have in mind is a type that's a list of byte ... The value is defined as the concatenation of these arrays. ... > way to concatenate a bunch of strings. ...
      (comp.lang.python)
    • Re: deepening into fortran 90,95, 2003
      ... I've been bitten many times by the slowness of concatenation of strings. ... Support the Original G95 Project: http://www.g95.org ... Support the GNU GFortran Project: http://gcc.gnu.org/fortran/index.html ...
      (comp.lang.fortran)
    • Re: concat vs variable in string
      ... Or does it still do a concatenation behind the scenes? ... If you use single quotes instead of double, ... massive strings or a long loop. ... to be wrapped in double quoted strings, ...
      (comp.lang.php)
    • Re: From D
      ... string concatenation and allow numeric literals to implicitly concatenate? ... Did you miss the bit where Python ALREADY does this for strings? ... because two int tokens can be "concatenated" to make a single int token, ...
      (comp.lang.python)