How to validate mysql database URI

Question

I'm trying to integrate a Gem named blazer with my Rails application and I have to specify mysql database URL in blazer.yml file so that it can access data in staging and production environments.

I believe the standard format to define MySQL database URL is

mysql2://user:password@hostname:3306/database

I defined my URL in the same format as a string and when I validate the URI I get the below error

URI::InvalidURIError: bad URI(is not URI?): mysql2://f77_oe_85_staging:LcCh%264855c6M;kG9yGhjghjZC?JquGVK@factory97-aurora-staging-cluster.cluster-cmj77682fpy4kjl.us-east-1.rds.amazonaws.com/factory97_oe85_staging

Defined Mysql database URL:

'mysql2://f77_oe_85_staging:LcCh%264855c6M;kG9yGhjghjZC?JquGVK@factory97-aurora-staging-cluster.cluster-cmj77682fpy4kjl.us-east-1.rds.amazonaws.com/factory97_oe85_staging'

Please advice

Answer 1

The URI is invalid.

The problem is the password contains characters which are not valid in a URI. The username:password is the userinfo part of a URI. From RFC 3986...

  foo://example.com:8042/over/there?name=ferret#nose
  \_/   \______________/\_________/ \_________/ \__/
   |           |            |            |        |
scheme     authority       path        query   fragment

authority   = [ userinfo "@" ] host [ ":" port ]
userinfo    = *( unreserved / pct-encoded / sub-delims / ":" )

pct-encoded = "%" HEXDIG HEXDIG
unreserved  = ALPHA / DIGIT / "-" / "." / "_" / "~"
sub-delims  = "!" / "$" / "&" / "'" / "(" / ")"
            / "*" / "+" / "," / ";" / "="

Specifically it's the ? in the password LcCh%264855c6M;kG9yGhjghjZC?JquGVK. It looks like the password is only partially escaped.

Answer 2

I think a problem is the issue is not well isolated. Here is an example strategy of how to isolate it.

The error code of URI::InvalidURIError: bad URI(is not URI?): only indicates the library (blazer gem) successfully read a file, which may or may not be the file you have edited, /YOUR_DIR/blazer.yml or something, but nevertheless failed to parse the URI.

Now, the issues to consider include:

1. blazer gem really read /YOUR_DIR/blazer.yml?
2. does the preprocessor of the yml work as expected?
3. is the uri key specified correct?
4. mysql: or mysql2?
5. are the formats of IP, port, account name, password, and database name all correct? In particular, are special characters correctly escaped? (See MySql document about special characters)

I suppose the OP knows answers of some of these questions but we don't. So, let's assume any of them can be an issue.

Then a proposed strategy is this:

1. Find a URI that is at least in a correct format and confirm it is parsed and recognised correctly by Gem blazer. Note you only need to test the format and so dummy parameters are fine. For example, try a combination of the following and see which does not issue the error URI::InvalidURIError:

   1. mysql://127.0.0.1/test
   2. mysql://adam:alphabetonly@127.0.0.1/test
   3. jdbc:mysql://adam:alphabetonly@127.0.0.1/test

   Now, you know at least the potential issues (1),(3),(4) are irrelevant.

2. Replace the IP (hostname), account name, password, and database name one by one with the real one and find which raises the error URI::InvalidURIError. Now you have narrowed down which part causes a problem. In the OP's case, I suspect the problem is an incorrect escape of the special characters in the password part. Let's assume that is the case, and then,

3. properly escape the part so that they form a correct URI format as a whole. The answer by @Schwern is a good summary about the format. As a tip, you can get an escape URI by opening Rail's console (via rails c) and typing URI.encode('YOUR_PASSWORD') or alternatively, run ruby directly from the command-line in a (UNIX-shell) terminal:

   ruby -ruri -e "puts URI.encode('YOUR_PASSWORD')"
   

   Replace the password part in the URI in /YOUR_DIR/blazer.yml with the escaped string, and confirm it does not issue the error URI::InvalidURIError (hopefully).

In these processing, I deliberately avoided the preprocessor part, (2). This answer to "Rails not parsing database URL on production" mentions about URI.encode('YOUR_PASSWORD') in a yml file, but it implicitly assumes a preprocessor works fine. During the test phase, that just adds another layer of complication, and so it is better to skip it. If you need it in your production (to mask the password etc), implement it later, when you know everything else works fine.

Hope by the time the OP has tried all of these, the problem is solved.