May 22, 2015 at 1:05 pm
HI Guys,
I'm automating some of the TDE stuff and while doing so stuck somewhere. I'm unable to use section [ENCRYPTION BY PASSWORD = ''"' + @db_pswd + '"'' ] properly as in if the user (DBA) wants to pass a single quote(') in the password, then it will fail.
I used quoted_identifier OFF/ON outside my proc, no luck still. Please suggest what is becoming wrong here
--Create a backup of the certificate with a private key and store in a secure location
select @cmd = 'BACKUP CERTIFICATE ' + @cert_nm + ' TO FILE = ''' + @cert_file + '''' +
' WITH PRIVATE KEY ( FILE = ''' + @key_file + ''', ENCRYPTION BY PASSWORD = ''"' + @db_pswd + '"'' );'
select @cmd
--EXEC sp_executesql @cmd
Thanks.
May 22, 2015 at 1:11 pm
SQL-DBA-01 (5/22/2015)
HI Guys,I'm automating some of the TDE stuff and while doing so stuck somewhere. I'm unable to use section [ENCRYPTION BY PASSWORD = ''"' + @db_pswd + '"'' ] properly as in if the user (DBA) wants to pass a single quote(') in the password, then it will fail.
I used quoted_identifier OFF/ON outside my proc, no luck still. Please suggest what is becoming wrong here
--Create a backup of the certificate with a private key and store in a secure location
select @cmd = 'BACKUP CERTIFICATE ' + @cert_nm + ' TO FILE = ''' + @cert_file + '''' +
' WITH PRIVATE KEY ( FILE = ''' + @key_file + ''', ENCRYPTION BY PASSWORD = ''"' + @db_pswd + '"'' );'
select @cmd
--EXEC sp_executesql @cmd
Take a look at the value of @cmd before you run it. You will need to replace any single quotes with double quotes because you are doing this inside dynamic sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 22, 2015 at 1:51 pm
I'm thinking the problem is using " (double quotes) in building your string and probably not the correct number of ' (single quotes).
May 22, 2015 at 2:22 pm
Problem is , if someone likes to password (for e.g.2peUj2Rj'jjS7'h) which has multiple single quotes, then only.....
Thanks.
May 22, 2015 at 2:27 pm
SQL-DBA-01 (5/22/2015)
Problem is , if someone likes to password (for e.g.2peUj2Rj'jjS7'h) which has multiple single quotes, then only.....
Then use REPLACE.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 22, 2015 at 2:28 pm
Lynn Pettis (5/22/2015)
I'm thinking the problem is using " (double quotes) in building your string and probably not the correct number of ' (single quotes).
Oh boy...looking back at my post it did not say what I meant at all. I meant to replace the single quotes with two single quotes. I think it is time for the long weekend.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 27, 2015 at 8:03 am
Thanks Sean....I really needed a holiday. Last week visited Chicago Botanic Garden...Anyways!! 🙂
I changed the logic of passing the password now, as using two single quotes too did not help me because if in the string (passwed) if tge DBA/user passes one or two quotes, could be a trouble.
Now, I have written something where in password will be generated randomly and no user input with NO single quotes in it. Fairly easier for me to manage.
But, as I mentioned I wrote an automated script to perform the entire TDE work
Thanks.
May 27, 2015 at 8:11 am
Just out of interest is there a rule or convention on what these symbols are called? Strictly speaking,
' is an apostrophe, and
" is a quote.
Confusion sets in when terms like "single quote" and "double quote" are used.
May 27, 2015 at 8:28 am
SQL-DBA-01 (5/27/2015)
if in the string (passwed) if tge DBA/user passes one or two quotes, could be a trouble.
Not if you use REPLACE.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 27, 2015 at 8:45 am
Richard Warr (5/27/2015)
Just out of interest is there a rule or convention on what these symbols are called? Strictly speaking,' is an apostrophe, and
" is a quote.
Confusion sets in when terms like "single quote" and "double quote" are used.
That isn't quite correct. The apostrophe can be denoted by either ’ or '. And both ' and " are quotation marks but have different meanings.
http://en.wikipedia.org/wiki/Quotation_mark
And if you look up the HTML codes for these marks it really sheds some light. The ' is referred to as a single quote.
http://www.ascii.cl/htmlcodes.htm
It is highly confusing especially when somebody (cough cough) accidentally says double when they meant single. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 28, 2015 at 5:20 am
I think I prefer [/url] as a better reference for character naming.
0022, ", is QUOTATION MARK.
0027, ', is APOSTROPHE.
The other two you mentioned are 2017 and 2018, LEFT and RIGHT SINGLE QUOTATION MARK. These are more for word processing. As far as I know, they don't have any special meaning re interpretation of string literals by SQL or any other programming language.
That being said, when discussing this in our office, we tend to call ' and " as single quotes and double quotes. If you need to double them, because you're inside a string literal usually, then we say doubled, as in a doubled single quote.
May 28, 2015 at 5:33 am
If you look at the OP and type the solution on a semi-normal keyboard while not trying to screw it up, then Gail's advice works fine: Use REPLACE.
May 28, 2015 at 6:51 am
dale_berta (5/28/2015)
I think I prefer [/url] as a better reference for character naming.0022, ", is QUOTATION MARK.
0027, ', is APOSTROPHE.
The other two you mentioned are 2017 and 2018, LEFT and RIGHT SINGLE QUOTATION MARK. These are more for word processing. As far as I know, they don't have any special meaning re interpretation of string literals by SQL or any other programming language.
That being said, when discussing this in our office, we tend to call ' and " as single quotes and double quotes. If you need to double them, because you're inside a string literal usually, then we say doubled, as in a doubled single quote.
This just further proves the confusion. Even in your list it has "apostrophe-quote" and "APL quote" as alternate names.
In common speaking I tend to call them tick marks or double ticks when they need to be escaped. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply