Sqlexec() Updating SQL with an Encrypted string.

[hjohnson]

I'm sure the answer is staring me in the face, but I'm having an issue when trying to update a field in a SQL table with a string that has been encrypted. I get a message saying --

Unclosed quotation mark before the character string 'Ò«·
'.

What I find odd is that I also have view doing the same thing. The View seems to handle it without a problem, but i can't seem to get the SQLEXEC to do the same thing.

Anybody have a idea how the View would be treating this differently than SPT?

Thanks for you time-
Hal

 

[bborissov]

Can you post your actual code?
What happens if you use parameters instead of build the whole string?

lcEncryptedString = [asdasdasdasd'asdasdasd]
lcSQL  = [UPDATE MyTable SET Field1 = ?lcEncryptedString]
IF SQLEXEC(lnSQLHandler, lcSQL) < 0
   AERROR(laError)
   MESSAGEBOX(laError[1,2])
ENDIF

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Mike Lewis]

Hall,

The problem is that your encrypted strig contains one or more apostrophe, which the server interprets as a string delimiter.

The solution is to place an extra apostrophe before each existing apostrophe.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[hjohnson]

Thanks for the quite replies.
The Encrypted string does not contain any apostrophes, I was hopeful for this as it would have been a rather simple change.

I did try the using the parameters and it almost worked... At least I didn't get the error. The problem appears to be one of the Ascii in the 5th position as only the first 4 of the 19 characters are updating. The ASC() of character in the 5 position is 1 (equivalent to HOME key) which apparently does something to mess things up. I can't even copy/Paste the string. I'm not sure how the Update in the VIEW would treat this differently as it doesn't have a problem.

 

[hjohnson]

On additional odd behavior noticed with the encrypted string .. Actually this is spooky.. is that in an update of multiple fields, the fields that follow the encrypted field are being offset by one field..

Example: For better understand here is the setup.
This particular data is for storing credit card information. The SQL fields (and their actual order) are '..., Cardnumber, Exp_date, Card_name, Card_addr, card_city, etc..)

After the update to sql I View the data using Query Analyzer (Select * from ..) and noticed that the Exp_date was empty, but the card_name field appears to contain '12/07', the expected value for exp_date. Card_name is now in the Card_addr field and so on for the remaining fields. If I selected just fields that looked off, the data was normal so whatever the ascii character is doing, it affects SQL Query Analyzer as well.

The query string for the update is built from an VFP data. I'm scattering to a Name and then looping through to build the query string substituting with parameters. bewlow is an example of the query

Update Ctrans set ORD_NO = ?oSQL.ORD_NO , AMOUNT = ?oSQL.AMOUNT ,CARD_2ADDR = ?oSQL.CARD_2ADDR ,CARD_ADDR = ?oSQL.CARD_ADDR ,CARD_CITY = ?oSQL.CARD_CITY ,CARD_NAME = ?oSQL.CARD_NAME ,CARD_ST = ?oSQL.CARD_ST ,CARD_ZIP = ?oSQL.CARD_ZIP ,TRANSDATE = ?oSQL.TRANSDATE ,EXP_DATE = ?oSQL.EXP_DATE ,CARDNUMBER = ?oSQL.CARDNUMBER ,ORD_NO = ?oSQL.ORD_NO WHERE ctrans.pkiden=254200


Thanks
Hal