Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

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.

share|improve this answer
    
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 Sep 11 '13 at 8:37
    
@PinguinDirk you are right! Let me think about. I'll try some help from Wolfram support. –  Murta Sep 11 '13 at 11:21
    
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 Sep 11 '13 at 12:22
    
I have all interest in this problem. I came back as soon as I get some news from Enterprise Support. –  Murta Sep 11 '13 at 13:34
    
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 Sep 12 '13 at 7:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.