OpenSQLConnection problem - password visible

9

4

Prerequisites

  • some database (here, it's called sakila (mysql standard test db I think))
  • a user (with password) that has access to that db (I'll use u:testuser/p:testpass)

Setup - minimal (?) working example

We call

 Needs["DatabaseLink`"]

Next we shall use the wizard/connection tool to create a new database connection, like:

c1 = OpenSQLConnection[]

enter image description here

(after creating the new connection, we use the wizard to connect right to the new connection; and obviously, we do not tick the "store password in plain text").

Next, we set up another connection:

c2 = OpenSQLConnection["test"]

where "test" is the name we used for above connection (we get prompted for the password).

Finally, a third connection:

c3 = OpenSQLConnection[
    JDBC["MySQL(Connector/J)", "localhost:3306/sakila"], 
    "Catalog" -> Automatic, "Description" -> None, "Name" -> "test", 
    "Password" -> "$Prompt", "Properties" -> {}, "ReadOnly" -> False, 
    "RelativePath" -> False, 
    "TransactionIsolationLevel" -> "ReadUncommitted", 
    "UseConnectionPool" -> False, "Username" -> "testuser", 
    "Version" -> 2.]

(this is exactly copy/paste from the .m file created by the wizard, using OpenSQLConnection instead of SQLConnection. You can check the FullForm of c1 or c2 to find its location, on MacOSX, standard: "/Users/username/Library/Mathematica/DatabaseResources/test.m").

And maybe a last one (choose "test" in the GUI):

c4 = OpenSQLConnection[]

The Problem

Execute:

Cases[#, HoldPattern[Rule["Password", ___]], Infinity] & /@ {c1, c2, c3, c4}

{{"Password" -> "testpass"}, {"Password" -> "\$Prompt"}, {"Password" ->"\$Prompt"}, {"Password" -> "testpass"}}

i.e., c1 and c4 (the connections I opened with OpenSQLConnection[] (without argument)) are sharing my password in plain text!

My Question

Is this a known behaviour? I was using OpenSQLConnection[] (using the GUI) frequently, am I doing something wrong?

Edit See @Murta's answer for an example where the password is visible without using the GUI.

Pinguin Dirk

Posted 2013-09-10T17:52:58.630

Reputation: 6 391

Answers

4

When I have to connect frequently to a database, I prefer to create a pack with the connections strings.

BeginPackage["myConn`",{"DatabaseLink`"}]

myConn::usage="myConn[ip] with default 192.168.0.20";

Begin["`Private`"]

myConn[ip_:"192.168.0.20"] := 
  OpenSQLConnection[
   JDBC["Microsoft SQL Server(jTDS)", ip], 
   "Username" -> "my User", 
   "Password" -> Uncompress["1:eJxTTMoPChZgYGDIrXTOz8tLTS7JzM8LSCwuBgBdFAg1"] ];

SetAttributes[marcheConn, {ReadProtected,Locked}];
(*Locked is important to don't permit ReadProtected to be undone*)

End[]
EndPackage[]

Then I encode the package as you can see here to protect the package content.

I can now use my connection as in this example:

Needs["myConn`"]
conn=myConn[]
data=SQLExecute[conn,"select * from tabX"]
CloseSQLConnection[conn]

Update

As commented by @PinguinDirk you can get all conn information (password and login) simple executing conn. I wrote to Wolfram Enterprise Support that answered:

I want to let you know that we were able to reproduce that the password was visible when the FullForm of the expression is requested in Mathematica (or the execution of conn). Currently, we are unable to uncover a method that would protect this information from being visible in a Mathematica session once the connection has been established.

Maybe in version 10. I have officially suggested it.

Murta

Posted 2013-09-10T17:52:58.630

Reputation: 23 859

Thanks for this approach - one problem: when I do so (created package (mySQL connection), encoded it, opened that connection), the password is still visible in FullForm (even Uncompressed). Can you confirm that this is the case for you too? ... maybe I did something wrong, just want to make sure, and try again – Pinguin Dirk – 2013-09-11T08:37:03.443

@PinguinDirk you are right! Let me think about. I'll try some help from Wolfram support. – Murta – 2013-09-11T11:21:54.803

I find it rather scary... let me know what Wolfram thinks, I'll wait to write them until I hear back from you. Thanks for the effort – Pinguin Dirk – 2013-09-11T12:22:22.170

I have all interest in this problem. I came back as soon as I get some news from Enterprise Support. – Murta – 2013-09-11T13:34:03.103

Thanks for the update - currently I connect to my db's in a way like c2 or c3 in my post, and then the password is not visible... (as I don't store those important passwords, this is a valuable option for me, i.e. using "$Prompt"). So I am more or less safe, but well, they better fix this for all sorts of ways to connect. – Pinguin Dirk – 2013-09-12T07:34:50.613

In the meantime doesn't closing con inside the package avoid this problem? i.e. just pass a sql string to the package, SQLExecute it in the package, and pass back only the result. Ok its a little inefficient if you have multiple SQL statements to run but avoids the issue if you don't want to use $Prompt. – Gordon Coale – 2014-08-13T16:21:38.990