Escape special characters within jdbc connection string

[andrew_swanton]

Hi,

The password I use to connect to my database has recently changed and now includes some special characters, which cause my connection to fail.

My current set up involves added a connection key to the noe4j.conf like so apoc.jdbc.mykey.url=jdbc:oracle:thin:USER/PASSWORD@HOST:PORT/SERVICENAME

Then accessed my data as follows

CALL apoc.load.jdbc('mykey',
 'select * from dual',
[],
{}) YIELD row RETURN row

However, the password on the database has now changed an now contains some special characters 'P@SSW*RD' (note the @ and *), when I update the config with the new password (apoc.jdbc.mykey.url=jdbc:oracle:thin:USER/P@SSW*RD@HOST:PORT/SERVICENAME)I can no longer connect.

CALL apoc.load.jdbc('mykey',
 'select * from dual',
[],
{}) YIELD row RETURN row

gives the error

Neo.ClientError.Procedure.ProcedureCallFailed: Failed to invoke procedure `apoc.load.jdbc`: Caused by: oracle.net.ns.NetException: Invalid connection string format, a valid format is: "host:port:sid" 

ie it thinks that the @ in P@SSW*RD is the indicating the start of the host name

Is there a way of escaping the 'bad' characters within my connection string. Or an alternative method I can use connect to my database which does not involve hard coding my credentials into my cypher script

[dana_canzano]

this appears to be an issue with the Oracle JDBC driver in how it parses the connection url. Have you asked Oracle for guidance?

https://community.smartbear.com/t5/SoapUI-Open-Source/JDBC-Connection-String-Issue-with-the-password... reports the same relative to a '@' in the connection URL for the password

[andrew_swanton]

I've not spoken to Oracle (will try and do so soon), however most of the documentation suggests passing in the username and password as separate parameters when establishing a connection.
Connection conn = DriverManager.getConnection ("jdbc:oracle:dnlthin:@database","user","password");

I suspect that this is what happens when the credentials parameter is in apoc.load.jdbc is used. And indeed when I call apoc.load.jdbc with the credentials parameter it works

CALL apoc.load.jdbc('jdbc:oracle:thin:@HOST:PORT/SERVICE_NAME',
'select * from dual',
[],
{credentials:{user:'USER', password: 'P@SSW*RD'}}) YIELD row RETURN row

However, doing so requires me to write credentials into the script. Which isn't very secure and if I have to update the password in the future I will have to update it in all of my scripts.

Is there a way I could store the credentials somewhere on the server (possibly within neo4j.conf or maybe as a csv inside the import, somewhere else) so that I could retrieve and use them at runtime.

EDIT:
I think the ability within neo4j.conf to set apoc.jdbc.mykey.password=P@SSW*RD and apoc.jdbc.mykey.user=USER would be an elegant solution in keeping with the current framework.

[tom_russell]

@andrew.swanton did you ever figure out ensuring your jdbc connection was secure? I've had similar trouble recently as well. I used the neo4j.conf route allow just with the full connecction string and not splitting out the user and password. That said, we had some concerns when the query errors out (i.e. bad sql or connection), it will return an error message with the raw full connection string in the error message exposing the authentication. Have you found a way around this?