on 2017 Apr 27 7:43 AM
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?
Request clarification before answering.
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 🙂
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
User | Count |
---|---|
39 | |
15 | |
9 | |
7 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.