locked
Triple single quote RRS feed

  • Question

  • Hello,

    Sometimes, I see examples in T-SQL with triple single quote. can you explain this please?

    SET

    @Param=' INNER JOIN Parameters PT on Rel.Project =pt.Project and PT.Host='''+@Host+''' and PT.HostID='''+@HostID+''' '

    Tuesday, August 7, 2012 2:51 PM

Answers

  • Or simply use the QUOTENAME function?

    SET @Param=' INNER JOIN Parameters PT on Rel.Project = pt.Project and PT.Host = ' + QUOTENAME(@Host, '''') + ' and PT.HostID = ' + QUOTENAME(@HostID, '''')
    
    
    
    

    It will guard you against sql injection and also will protect you from data containing brackets and single quote character's such as    Mark's Place.


    N 56°04'39.26"
    E 12°55'05.63"

    • Marked as answer by RobbieDotNet Wednesday, August 8, 2012 12:18 PM
    Wednesday, August 8, 2012 7:48 AM
  • But in this case we would have four single quotes before AB and you were saying " will become ' so we will have two single quotes here.

    Please clarify. I know it might be easy but for some reason I am not getting it.

    Hi

    In this Example ;

    EXEC( 'SELECT * FROM sys.database_principals WHERE name = ''' + @name +'''' )

    3 single quotes becomes 1. Say @name = 'Joe', then the above statement will resolved to

    SELECT * FROM sys.database_principles where name = 'Joe'. The left and right most single quotes are needed to identify the string literal that is the query itself.Hope this example clarifies things more.

    If you still have any questions about how this works in a particulat scenario, could you please post an example ? Its really difficult to comeup with a generic clarification and I would prefer to tailor my reply as applicable to your situation


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    • Marked as answer by RobbieDotNet Wednesday, August 8, 2012 12:18 PM
    Tuesday, August 7, 2012 7:48 PM
  • But in this case we would have four single quotes before AB and you were saying " will become ' so we will have two single quotes here.

    Please clarify. I know it might be easy but for some reason I am not getting it.

    Hi,

    We pass a script like

    'select col1 from tab1 where 1=1 '

    as the dynamic sql.

    And the main point is, for any single quote in this must be double quote within the dynamic script which we pass to run

    select col1 from tab1 where 1=1

    for instance this script can be

    select col1 from tab1 where col2='A'

    next time,

    So it must be this

    select col1 from tab1 where col2=''A''

    that is to be passed as the correct dynamic script!


    Many Thanks & Best Regards, Hua Min



    • Edited by Jackson_1990 Wednesday, August 8, 2012 8:46 AM
    • Marked as answer by RobbieDotNet Wednesday, August 8, 2012 12:18 PM
    Wednesday, August 8, 2012 8:44 AM

All replies

  • It means when running that it is actually running this

    INNER JOIN Parameters PT on Rel.Project =pt.Project and PT.Host='AB' and PT.HostID='ABC'

    supposing @Host is 'AB' and @HostIDis 'ABC'. That is, '' will become ' after the translation by Sql server engine.


    Many Thanks & Best Regards, Hua Min




    • Edited by Jackson_1990 Tuesday, August 7, 2012 3:03 PM
    • Proposed as answer by ank hit Tuesday, August 7, 2012 3:08 PM
    Tuesday, August 7, 2012 3:02 PM
  • Basically if you want to see the effect of such things, always use the same thing inside the PRINT() statement and try to play with it so you can have good understanding with it. Generally we use such things inside the EXEC statement or when you have parameters or dynamic sql. from your above statement you want to join PT.HOST with the value inside @host parameter, since it is a value, it must be like 'some value'. that means it contains one quote each side. Its a basic syntax which we need to follow to play around such things. I hope this will help you. Let me know if you have any question on this.

    ANK HIT


    • Proposed as answer by ank hit Tuesday, August 7, 2012 3:07 PM
    • Edited by ank hit Tuesday, August 7, 2012 3:08 PM
    Tuesday, August 7, 2012 3:07 PM
  • Hi,

    The triple single quotes are actually escape sequence for a single - single quote. In the example that you have here, you are dynamically generating the query string on the fly, so during compile time, after the parameters have been resolved , the query gets transformed as explained in HuaMin Chen's reply. Please go through this MSDN blog post for a more detailed explanation of the same - http://blogs.msdn.com/b/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    Tuesday, August 7, 2012 3:16 PM
  • But in this case we would have four single quotes before AB and you were saying " will become ' so we will have two single quotes here.

    Please clarify. I know it might be easy but for some reason I am not getting it.

    Tuesday, August 7, 2012 5:09 PM
  • But in this case we would have four single quotes before AB and you were saying " will become ' so we will have two single quotes here.

    Please clarify. I know it might be easy but for some reason I am not getting it.

    Can you please post some example values stored in those two parameters?

    ANK HIT

    Tuesday, August 7, 2012 6:16 PM
  • But in this case we would have four single quotes before AB and you were saying " will become ' so we will have two single quotes here.

    Please clarify. I know it might be easy but for some reason I am not getting it.

    Hi

    In this Example ;

    EXEC( 'SELECT * FROM sys.database_principals WHERE name = ''' + @name +'''' )

    3 single quotes becomes 1. Say @name = 'Joe', then the above statement will resolved to

    SELECT * FROM sys.database_principles where name = 'Joe'. The left and right most single quotes are needed to identify the string literal that is the query itself.Hope this example clarifies things more.

    If you still have any questions about how this works in a particulat scenario, could you please post an example ? Its really difficult to comeup with a generic clarification and I would prefer to tailor my reply as applicable to your situation


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    • Marked as answer by RobbieDotNet Wednesday, August 8, 2012 12:18 PM
    Tuesday, August 7, 2012 7:48 PM
  • The last quote closing the string variable and the double quote is to insert a quote (single quote needs to be doubled in a string variable).

    So, the whole string will become

    inner join Parameters PT on Rel.Project = pt.Project and PT.Host = 'Some Host'

    However, this is not good way to construct dynamic SQL. In dynamic SQL we don't want to embed parameters into the string.

    I suggest you to check this blog post

    Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by Sanil Mhatre Tuesday, August 7, 2012 9:56 PM
    Tuesday, August 7, 2012 9:22 PM
  • But in this case we would have four single quotes before AB and you were saying " will become ' so we will have two single quotes here.

    Please clarify. I know it might be easy but for some reason I am not getting it.

    Hi,

    We pass a script like

    'select col1 from tab1 where 1=1 '

    as the dynamic sql.

    And the main point is, for any single quote in this must be double quote within the dynamic script which we pass to run

    select col1 from tab1 where 1=1

    for instance this script can be

    select col1 from tab1 where col2='A'

    next time,

    So it must be this

    select col1 from tab1 where col2=''A''

    that is to be passed as the correct dynamic script!


    Many Thanks & Best Regards, Hua Min



    • Edited by Jackson_1990 Wednesday, August 8, 2012 8:46 AM
    • Marked as answer by RobbieDotNet Wednesday, August 8, 2012 12:18 PM
    Wednesday, August 8, 2012 8:44 AM