topic Re: QVD files store connection string in plain text! in QlikView

QVD files store connection string in plain text!

pljsoftware — Tue, 06 Dec 2011 16:26:37 GMT

Hi all!

I have noticed a very strange thing using QVD files... Inside the file, I can read in plain text my QlikView script, including the connection string with password!

How it can be possible?

QVD files store connection string in plain text!

fosuzuki — Wed, 07 Dec 2011 15:50:55 GMT

Wow, I did not know this.

I think you should try to get in touch with QlikTech...

Regards,

Fernando

QVD files store connection string in plain text!

erichshiino — Wed, 07 Dec 2011 16:01:33 GMT

HI, Fernando.

Did you get the same results?

Which version are you all using?

Regards,

Erich

QVD files store connection string in plain text!

fosuzuki — Wed, 07 Dec 2011 16:11:09 GMT

Hi Erich, yes same problem. Using QV10SR2.

Miguel, at first I did not believe (sorry Luca, nothing personal... ), but it is true. Try opening the QVD file in notepad. In the XML strucutre, there is something like this:

<Discriminator>Provider=SQLOLEDB.1;Persist Security Info=True;User ID=XXXX;Initial Catalog=catalog;Data Source=XXXX;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=XXXX;Use Encryption for Data=False;Tag with column collation when possible=False;Password=XXXX</Discriminator>

<Statement>Data:

SQL SELECT....

Fernando

Re: QVD files store connection string in plain text!

Miguel_Angel_Baeyens — Wed, 07 Dec 2011 16:20:19 GMT

Fernando,

I'm afraid I'm missing something. I already did, but I don't see those XML tags (LineageInfo, Discriminator) in my QVD files. Are you doing a LOAD and then STORE or you STORE directly from a SQL statement? I always LOAD then STORE.

Thanks and regards,

Miguel

EDIT: My God... I'm sorry Fernando, I was kind of dumb. I do see the tags you mention, and they indeed are plain text even when I've set them scrambled in the Edit Script dialog, Settings tab in the bottom part. It's definitely a support case.

Thanks Jonathan for noting!

BTW using 10 SR4. That does not happen with version 9 (x64 SR4), and hence my confussion, since I have QVD created with a lot of different versions of QlikView.

EDIT2: No so blind: I do see the Lineage and Discriminator fields, and sometimes the user, but never the password neither plain text nor scrambled. Using both OLEDB and ODBC drivers. Can you please elaborate on how are you creating your connection?

Update: ODBC: when user and password is stored in the ODBC (so there's no need to specify when selecting with the Connect... button, no passwords are visible). OLEDB: I can see the user in plain text (but it does is plain text in the connection string), but never the password. My tests connecting to a Microsoft SQL Server.

Re: QVD files store connection string in plain text!

fosuzuki — Wed, 07 Dec 2011 16:52:58 GMT

Miguel, no problem. Glad to know I wasn't wrong... Same here: I use scrambled password in script, but it appears unscrambled in QVD... Also checked old QVD files generated by QV9SR4 and there isn't such XML section.

Re: QVD files store connection string in plain text!

johnw — Wed, 07 Dec 2011 21:25:34 GMT

EDIT: LOL, I AM kind of dumb! I didn't RUN the script, and I was grabbing QVDs from our development system, where some of them are quite old. The new QVD does indeed have plain text user ID and password despite me asking for it to be scrambled. Nasty little security hole there!

Original Post: OK, I guess it's my turn to be "kind of dumb". I don't see those XML tags, user ID or password in my QVDs, or at least in the ones I've looked at so far. I'm using V10 SR3. Here's our simplest QVD script with the scrambled password:

DIRECTORY QVD;

ODBC CONNECT TO UPIDB2P (XUserId is NSUDeYRNJbaMXUVMXDbA, XPassword is NQcXLYFMTbcMXUVMXDLA);

[Cost System Start Date]:
LOAD date(date#(START,'YYYYMMDD')) as "Cost System Start Date"
;
SQL SELECT SUBSTR(BPDATA,50,8) AS START
FROM SYSTLC.ATBP101R
WHERE BPID = 'COST SYSTEM'
FOR FETCH ONLY
;
DISCONNECT;

STORE [Cost System Start Date] INTO CostSystemStartDate.qvd (QVD);

And here's the resulting QVD:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<QvdTableHeader>
   <QvBuildNo>7646</QvBuildNo>
   <CreatorDoc></CreatorDoc>
   <CreateUtcTime>2011-05-02 23:07:21</CreateUtcTime>
   <SourceCreateUtcTime></SourceCreateUtcTime>
   <SourceFileUtcTime></SourceFileUtcTime>
   <SourceFileSize>-1</SourceFileSize>
   <StaleUtcTime></StaleUtcTime>
   <TableName>Cost System Start Date</TableName>
   <Fields>
     <QvdFieldHeader>
       <FieldName>Cost System Start Date</FieldName>
       <BitOffset>0</BitOffset>
       <BitWidth>8</BitWidth>
       <Bias>0</Bias>
       <NumberFormat>
         <Type>3</Type>
         <nDec>0</nDec>
         <UseThou>0</UseThou>
         <Fmt>M/D/YYYY</Fmt>
         <Dec></Dec>
         <Thou></Thou>
       </NumberFormat>
       <NoOfSymbols>1</NoOfSymbols>
       <Offset>0</Offset>
       <Length>14</Length>
     </QvdFieldHeader>
   </Fields>
   <Compression></Compression>
   <RecordByteSize>1</RecordByteSize>
   <NoOfRecords>1</NoOfRecords>
   <Offset>14</Offset>
   <Length>1</Length>
</QvdTableHeader>
‘š 5/1/2008

Re: QVD files store connection string in plain text!

fosuzuki — Thu, 08 Dec 2011 10:23:28 GMT

Hi all,

I also found this same problem in the QVW files, at the end of the file you can find some XML text with the LineageInfo and Discriminator...

It seems that is was removed from QV11, but I don't have a QV11 instance to check it:

http://community.qlik.com/message/148804#148804

Re: QVD files store connection string in plain text!

johnw — Thu, 08 Dec 2011 17:16:56 GMT

Fernando Suzuki wrote:
I also found this same problem in the QVW files, at the end of the file you can find some XML text with the LineageInfo and Discriminator...

Yep, there it is. Good grief, QlikTech. Seriously?

Re: QVD files store connection string in plain text!

Miguel_Angel_Baeyens — Thu, 08 Dec 2011 19:02:38 GMT

That's a big one, indeed.

QVD files store connection string in plain text!

pljsoftware — Fri, 09 Dec 2011 11:48:06 GMT

Hi there,

I use QV 10 SR3, and when I open QVD file I found all my script and the password in plain text.

A few days ago I installed the version QV 11 RC and after I removed it and reinstalled QV 10 SR3, can this make me this problem?

Regards

Luca Jonathan Panetta

PLJ Software

QVD files store connection string in plain text!

Fri, 09 Dec 2011 15:11:36 GMT

I just tried it and I am speechless. I cannot believe such a disaster, this is very serious, I hope QT comes with a patch right away.

Nice discovery Luca.

Regards

QVD files store connection string in plain text!

isaiah82 — Fri, 09 Dec 2011 15:33:17 GMT

In every headache there is a blessing ~ I just used this to get back some "lost" credentials from a scrambled connection string that were never documented. For clarification, if there cred's are scrambled in your script, they are stored unscrambled in the qvd header.

One more reason not to keep fighting integrated authentication I suppose 😃

QVD files store connection string in plain text!

rbecher — Fri, 09 Dec 2011 15:46:27 GMT

Thanks, good to know!

I found also an unscrambled password (was scrambled in the script) at the end of the QVW created with QV10 SR3 in the xml metadata part:

<Discriminator>...

This is an unbelievable risky major bug!

- Ralf

Re: QVD files store connection string in plain text!

Anonymous — Fri, 09 Dec 2011 17:19:34 GMT

Wow! We have the same issue in QV10SR3. This doesn't thrill our SA's at all. Good thing our QVD's are locked down.

We had a good chuckle from Isiah Weed's post. Always a positive somewhere!!!!

QVD files store connection string in plain text!

rbecher — Sun, 11 Dec 2011 16:16:12 GMT

This seems to be fixed in QV10 SR4. I cannot reproduce it.

- Ralf

QVD files store connection string in plain text!

pljsoftware — Mon, 12 Dec 2011 10:18:48 GMT

Sorry Ralf,

I have try my test file with QV 10 SR4 9274 but the problem subsist.

I tried it with QV 9 SR7 7778 and it's all ok.

My script is:

OLEDB CONNECT TO .... //Connection String

CustomerSellers:

LOAD *;

SQL SELECT top 1 c.CardCode FROM OCRD c;

DISCONNECT;

load * Inline

[x,y

1,2];

STORE * from X into X.qvd;

EXIT Script;

Regards

Luca Jonathan Panetta

PLJ Software

Re: QVD files store connection string in plain text!

rbecher — Mon, 12 Dec 2011 11:06:08 GMT

I created a new QVW which stores some QVD files with QV10 SR4. It still stores the connect string but without the password.

So, I think this is not a problem anymore but of course maybe with old files ..

- Ralf

QVD files store connection string in plain text!

pljsoftware — Mon, 12 Dec 2011 11:38:25 GMT

Ok Ralf,

I tried too and the Password there isn't but why I have my script and my connection string in a QVD if I want only to save the data of my table?

In QV 9 SR7 7778 I have only my data.

I wrote to the support too.

Regards

Luca Jonathan Panetta

PLJ Software

QVD files store connection string in plain text!

Clever_Anjos — Mon, 12 Dec 2011 13:17:47 GMT

It´s a knowed bug in 10SR2 and 10SR3

Fixed on 10SR4