|
SQLAnywhere 17.0.6.2757 I've got a routine that generates random database passwords. The password is then set using a statement of the form: GRANT CONNECT TO "foobar" IDENTIFIED BY "generated_password" Note that my database has the option quoted_identifier=on yet I am able to use double quotes around the password as well as the userid (question on this below). Today this statement failed with the generated password shown below: GRANT CONNECT TO "foobar" IDENTIFIED BY "S_l`GelftCVO" giving the error: Syntax error near 'S_l`GelftCVO' on line 1 I have found that I can prevent the error by doing either of the following:
or
I have two questions on this behaviour: 1) why is the backquote allowed inside a single-quoted string and not a double-quoted string? 2) why am I able to use double quotes with passwords at all when quoted_identifier=on? |
|
1) Double-quoted strings are identifiers and must follow identifier rules. Single-quoted strings are just strings and have no restrictions on their content. You should be aware of the scanning rules for strings though: if you want a single-quote or a backslash within the string you should double them up. Better yet, if you are using a client app, use a host variable. 2) I'll just take a guess at this one that it's an old SQL standard "ism". It allows you do to things like GRANT CONNECT TO foo IDENTIFIED BY bar ... because putting quotes around 'bar' would be, um, ugly or time/space consuming or something :) Thanks for the answers John
(27 Apr '17, 07:35)
Luke
|
|
Examples for specifying a password identifier: CREATE USER SQLTester IDENTIFIED BY Welcome; CREATE USER SQLTester IDENTIFIED BY "Welcome"; CREATE USER SQLTester IDENTIFIED BY `Welcome`; CREATE USER SQLTester IDENTIFIED BY [Welcome]; Example for specifying a password literal: CREATE USER SQLTester IDENTIFIED BY 'Wel[come]'; Related Information GRANT CONNECT statement http://dcx.sap.com/index.html#sqla170/en/html/8170724f6ce21014ba8ac331f5428ab4.html Password and user ID restrictions and considerations http://dcx.sap.com/index.html#sqla170/en/html/814968c26ce21014a4d7bf69d2f74636.html Jack, thanks for enhancing the DCX doc topics accordingly (and another thanks for having such an "instantly improved" doc system...:)
(02 May '17, 03:44)
Volker Barth
1
You and everyone else can do the same. I often see useful tidbits contributed by folks like you to the forum. You can also add them via comments to the doc. Keep up the good work!
(02 May '17, 09:32)
JBSchueler
Replies hidden
Yes, I'm aware of that (and as you will know, I do add some comments now and then...)
(02 May '17, 10:07)
Volker Barth
|
AFAIK, passwords are not identifiers, so the syntax for GRANT CONNECT/CREATE USER might always expect a string literal following the "IDENTIFIED BY " phrase, no matter whether the string literal is surrounded by single or double quotes. Have you tried to double the back quote?
We do allow an identifier in the IDENTIFIED BY phrase. Backticks are not permitted in identifiers.
Ah, I see. Glad that I have used "AFAIK", now replaced by "AFAIOT" - "as far as I once thought":)