SQLBase API and Encryption

[markjack99]

I am trying to convert my SQL WIndows 2005.1 code to Team Developer 5.2. I had to adjust my C code to allow for UNICODE/Wide Characters. I believe I have done all of that properly. Below are the TD call to the C code and the Stored Procedure in my ADMIN database.

All this code worked in SQL Windows 2005.1

My issue is with retrieving my data when it is encrypted (using Windows CryptoAPI). It will not pull out the information properly. If I un-encrypt my date, my code pulls it out properly (e.g. 01272007). However, if i encrypt the data: ÞÏpè>œ<“ - the information retrieved is retrieved as: xxpx>x<x (the x's show up as boxes in any message box). It grabs the normal characters (the p, and the greater than and less than symbols but it can't get the special characters) (all characters are legal ANIS and UNICODE characters (http://www.alanwood.net/demos/ansi.html)

Anyone else have this issue? Anyone have a solution? Anyone have a suggestion on what to try?


Here is the Stored Procedure used to pull out the last logon date out of the ADMIN database:

Code: Select all

Call SqlStore(hSqlProcedure, 'SYSADM.GetLog',
      'Procedure: GetLog STATIC
      Parameters
         Receive String: strLog
      Local Variables
      Actions
         On Procedure Execute
            Call SqlImmediate(\'select LAST_LOGON_DATE FROM MISC_SECURITY into :strLog\')
            Call SqlClearImmediate()')

Here is the Team Developer 5.2 code used to call my C code:

Code: Select all

Call SalSetBufferLength(strDate, 500)
Set strDate = SpRetStrBindStr('ADMIN/' || str_USER_ID_1 || '/' || str_PASSWORD, 'SYSADM.GetLog',strDate)

Here is the C function which uses the SQLBase APIs to execute the Stored Procedure:

Code: Select all

LPWSTR CStoredProcsApp::SpRetStrBindStr (LPWSTR lpstrConnectString,LPWSTR lpstrStoredProcedure, LPWSTR lpstrBinds)
{
   SQLTCUR hSqlHandle;
   unsigned char nLength;
   unsigned char nFetRet;
   char strData[256];
   char *lp = strData;

   size_t i;
   static wchar_t wstrData[256];
   char mbsConnectString[256];
   char mbsStoredProcedure[256];
   char mbsBinds[256];
   char buffer[256];

   wcstombs_s(&i, mbsConnectString, (size_t)256, lpstrConnectString, (size_t)256);
   wcstombs_s(&i, mbsStoredProcedure, (size_t)256, lpstrStoredProcedure, (size_t)256);
   wcstombs_s(&i, mbsBinds, (size_t)256, lpstrBinds, (size_t)256);

   if (sqlcncA(&hSqlHandle, (SQLTDAP) mbsConnectString, 0) == 0)
   {
      if (sqlretA(hSqlHandle, (unsigned char *) mbsStoredProcedure, 0) == 0)
      {
         if (sqlbnn (hSqlHandle,1,(SQLTDAP) &strData, sizeof (strData),0,SQLPSTR) == 0)
         {
            sqlssb(hSqlHandle, (SQLTSLC)1, SQLPSTR, (SQLTDAP) &strData, 254, 0, &nLength,&nFetRet);
            sqlexe(hSqlHandle);
            sqlfet(hSqlHandle);
         }         
      }      
      sqldis(hSqlHandle);
   }
   mbstowcs_s(&i, wstrData, (size_t)256, strData, strlen(strData)+1);
   return wstrData;
}

;

[Jeff Luther]

My guess is you have not solved the Unicode issue, but I can't tell from what you've written. If you using native TD v5.2 with strings, everything has to be in Unicode. if one part is not Unicode -- the SP, for example, then you need to make sure the entire chain is in not in Unicode. You can't have part yes, part no.

How is the value being stored in the DB? Aha, hard to find... "CryptoAPI" -- that's ANSI, right?, so everything needs to be in ANSI, which means as soon as it comes into TD it needs to be converted to ANSI. Look at TD Help for SalStrToWideChar().

[markjack99]

We have converted absolutely everything to Unicode: TD 5.2 code, our C code, and sql api code.

Our encryption/decryption code works - we encrypt/decrypt values, they work properly.

When we hardcode TD sqlstatements to insert/select (in conjunction with our encryption functions) info to our database (sqlimmediates or sqlconnect/prepare/execute/fetch) - that works.

When we use TD to call our "modified" C code to use the sql/api functions to sqlcncW, sqlcomW (hardcoded our sql select statement here), sqlssb2, sqlexe, sqlfet - it worked.

When we use TD to call our C code - which uses the sql/api functions to sqlcncW, sqlretW (calling the Stored Procedure), sqlssb2, sqlexe, sqlfet - it does NOT work. It acts as if the sqlimmediates or sqlconnect/prepare/execute/fetch statements in the stored procedure are working on the ANSI level.

We built the stored procedure with the 11.5 Command Center and built it in TD code using the sqlStore statements. Both have the same results.

How do we ensure that the Stored Procedure is Unicode?

Thanks
Mark

[Jeff Luther]

Well, I'll ask internally, but I suggest a small test case (TD, app, SQL script to load test table + data + SP, etc.) to perhaps help further. I don't know if the SQL/API functions can or do work in Unicode, I suspect not but I'll ask.

[Jeff Luther]

I did get a response from one of the developers, who commented:

The first thing I would suggest is change the String parameter to WString.
So it’s: Receive WString: strLog

I'm not sure GetLog() is where your problem is, though, but I'll pass that along.

In other news... I read this in your post:

When we use TD to call our C code - which uses the sql/api functions to sqlcncW, sqlretW (calling the Stored Procedure), sqlssb2, sqlexe, sqlfet - it does NOT work. It acts as if the sqlimmediates or sqlconnect/prepare/execute/fetch statements in the stored procedure are working on the ANSI level.

My first thought then is, well, if that's what you think why not write a small TD test, and when the value is returned convert it to Unicode with SalStrToWideChar(). See Help for syntax, but if it appears it's ANSI then -- as I said earlier -- everything has to be that... until you are back in TD, then you convert that to Unicode, since TD v5.x only handles strings in Unicode.

[markjack99]

Jeff,

It seems you responded while I was typing this - I don't belive we knew there was a "Receive WString" - we assumed since TD was based in Unicode that all strings were Unicode (no need for the W) - we will try that tomorrow.



Here is the additional info I was preparing to send you:

We were able to get the SQL/API functions to work if we queried the database directly from them (i.e. sqlcomW), but we could not get them to work with the Stored Procedures (i.e. sqlretW).

This is why we are thinking the actual stored procedure is built on ANSI or is using an ANSI dll during execution. We're not sure which dlls would actually be responsible for building them or executing them.

We built the Stored Procedure in our TD 5.2 code using the sqlStore statement but it is probably using the SQLBase 11.5 dlls when running - just guessing here.

Thanks for looking into this for us.
Mark

[Jeff Luther]

Thanks for looking into this for us

Sure, but back in your court for testing to verify ANSI or not, Mark.

I don't belive we knew there was a "Receive WString"

Hmmm, I didn't either, but passed that along. Let us know if it is not correct

[markjack99]

Jeff

Receive WString fixed our problem

FYI: could not find it in any documentation

Thanks for the Help
Mark