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?

asked 27 Apr, 06:43

Luke's gravatar image

Luke
636111832
accept rate: 50%

edited 27 Apr, 06:58

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?

(27 Apr, 07:27) Volker Barth
Replies hidden
2

We do allow an identifier in the IDENTIFIED BY phrase. Backticks are not permitted in identifiers.

(27 Apr, 07:35) John Smirnios

Ah, I see. Glad that I have used "AFAIK", now replaced by "AFAIOT" - "as far as I once thought":)

(27 Apr, 07:38) Volker Barth

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 :)

permanent link

answered 27 Apr, 07:05

John%20Smirnios's gravatar image

John Smirnios
9.0k377114
accept rate: 39%

edited 27 Apr, 07:35

Thanks for the answers John

(27 Apr, 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

permanent link

answered 28 Apr, 10:08

JBSchueler's gravatar image

JBSchueler
2.1k2837
accept rate: 15%

edited 28 Apr, 10:48

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

(02 May, 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, 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, 10:07) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×11

question asked: 27 Apr, 06:43

question was seen: 195 times

last updated: 02 May, 10:07