dcsimg
Results 1 to 3 of 3

Thread: Security for passing parameters into commands

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jan 2012
    Posts
    25

    Security for passing parameters into commands

    I've read that when passing parameters to the commandtext method for the IDbcommand object I shouldn't build the command string as

    Code:
    command.CommandText = _
    "SELECT * FROM CUSTOMERS WHERE CITY = '" & _
    inputCity + "'";
    but instead use the SqlParameter object as this is more secure

    Code:
    command.CommandText = _
    "SELECT * FROM CUSTOMERS WHERE CITY =@City"
    my question is, why is this more secure because surely I'm assigning a string value to my parameter anyway. Are the two methods majorly different?

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,417

    Re: Security for passing parameters into commands

    There are a few reasons they are more secure, and several other reasons that they are better too.

    For an explanation of some of the reasons, see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of this forum).

  3. #3
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,220

    Re: Security for passing parameters into commands

    There is also one more advantage to using parameters and that is performance.

    To understand this advantage, you need to know what happens when you a query on the database.
    The query is parsed by the database.
    In an Oracle Database, this is done in two phases.
    1) Hard Parse
    2) Soft Parse

    The database must perform a hard parse instead of a soft parse if the parsed representation of a submitted statement does not exist in the shared pool. The hard parse takes more time and is more taxing compared to the softparse.

    When you choose to use command parameters, for subsequent sql queries, the DB does not have to do a hard-parse. So the execution speed increases.


    If you're interested in learning about binding / soft parse / hard parse, I suggest you read this book.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width