cancel
Showing results for 
Search instead for 
Did you mean: 
Read only

Passwords and quotes

Former Member
2,556

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:

  • removing the backquote character from the string

or

  • surrounding the password by single quotes (') rather than double

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?

Accepted Solutions (1)

Accepted Solutions (1)

johnsmirnios
Advisor
Advisor

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 🙂

Former Member
0 Kudos

Thanks for the answers John

Answers (1)

Answers (1)

jack_schueler
Product and Topic Expert
Product and Topic Expert

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

VolkerBarth
Contributor
0 Kudos

Jack, thanks for enhancing the DCX doc topics accordingly (and another thanks for having such an "instantly improved" doc system...:)

jack_schueler
Product and Topic Expert
Product and Topic Expert

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!

VolkerBarth
Contributor
0 Kudos

Yes, I'm aware of that (and as you will know, I do add some comments now and then...)