none
What is the best implementation ?? RRS feed

  • Question

  •  

    Hi everyone

     

    What is the best implementation from the following two examples from different point-of-views (Memory, Performance, security, and code readability) :

     

    "SELECT " + sFieldName + " FROM OptimaSys WHERE Code = '" + sCode + "'"

     

    or

     

    "SELECT @sFieldName FROM OptimaSys WHERE Code = @sCode"

     

    and then passing these parameters to the command and execute it.

     

     

    Any reply, discussion greatly appreciated.

    Sunday, November 9, 2008 8:35 AM

Answers

  • It's always better to use parameters. The reasons are:

    1. Security
    2. Performance
    3. You don't need to deal with characters that need escaping
    4. code readability.

    Have a look at this article too:
    SQL Injection Attacks and Some Tips on How to Prevent Them
    Monday, November 10, 2008 6:55 AM
  • Keep in mind that concatenating filed names or table names, or any object names cannot be done through parameters. Your second SQL statement will not work and will generate an error. You would need to use mix, like

     

    "SELECT " + sFieldName + " FROM OptimaSys WHERE Code = @sCode"

     

    but make sure that value of sFieldName is sanitized and not introducing any SQL injection vulnerability.

     

    Monday, November 10, 2008 12:00 PM
    Moderator

All replies

  • It's always better to use parameters. The reasons are:

    1. Security
    2. Performance
    3. You don't need to deal with characters that need escaping
    4. code readability.

    Have a look at this article too:
    SQL Injection Attacks and Some Tips on How to Prevent Them
    Monday, November 10, 2008 6:55 AM
  • Hi Giorgi

     

    Thanks for your reply

     

    It was a discussion between me and my team leader and he told me that parameters not make a performance improvement

     

    but actually as my experience grow with the CLR, I think that this style is more and more efficient, especially with string concatenation, I want to discuss this with you

     

    The + string + value operation make more string instances in memory and only the final one is the only used, as string object is mutable ? Is my believe right ?

     

    and of course security is a very big benefit as you state

     

    Thanks more for your reply my friend

     

    Mohamed

     

    Monday, November 10, 2008 8:39 AM
  • Hi

    My previous team leader also used to think like that.

    Building strings do add performance overhead but the real gain comes from the fact that most databases cache query execution plan of parametrized queries and if you execute the same parametrized query it will run faster. In case of strings nothing is cached in databases so queries execute slowly.

    Giorgi
    Monday, November 10, 2008 8:46 AM
  •  

    Hi Giorgi

     

    Right right my friend, you are right, you emphasize my knowledge experience

     

    Can you provide me with the most valuable improvements when dealing with Databases ( article or blog post )

     

    I really appreciate your collaboration

     

    Mohamed

    Monday, November 10, 2008 8:53 AM
  • Keep in mind that concatenating filed names or table names, or any object names cannot be done through parameters. Your second SQL statement will not work and will generate an error. You would need to use mix, like

     

    "SELECT " + sFieldName + " FROM OptimaSys WHERE Code = @sCode"

     

    but make sure that value of sFieldName is sanitized and not introducing any SQL injection vulnerability.

     

    Monday, November 10, 2008 12:00 PM
    Moderator
  • Hi VMazur

     

    Right and perfect addition.

     

    Is there any work around about this situation ?

     

    Thanks alot

     

    Monday, November 10, 2008 1:26 PM
  • No, concatenation is the only way to do this, but you need to sanitize the input, especially if it comes from the user

     

    Tuesday, November 11, 2008 11:12 AM
    Moderator