SQL and encrypted data gotcha

[Bob Sneidar]

Hi all. 

Those of you who are experienced developers will find this elementary, but I figured I would post it in case others run into this. I encrypt my passwords before sending them to the SQL database table of logins, using aes128 and a seed value that shall remain undisclosed. Works like a charm. But when I query the database for the password I noticed that at least a tab character is possible in the encrypted data, and I suppose commas as well. 

Imagine this scenario then: My select statement includes several non-encrypted columns of data along with the encrypted password column. I decide to return the data as a string, not an array. Unbeknownst to me, my encrypted data contains a comma or a tab. 

So to make things easier on myself, I replace tab with comma in the returned data. I then parse out the data into variables, item 1 to theUniqueID, item 2 to theUserName, etc and finally I come to the item containing the encrypted data. Guess what? It is now 2 or more items because of the commas and/or tabs. I will only get the data up to but not including the first tab or comma, depending on the delimiter. The decrypt command will if successful, put the decrypted results into the it variable, but because the encrypted data is incomplete, it silently fails and "it" remains whatever it was. Your login will fail but you won't know why. 

3 methods come to mind to get around this. 

1: Don't replace anything in a string containing encrypted data. Instead set the itemDelimiter to tab. Make sure the last column in the select statement is the encrypted data. Now parse all the other columns, but not the encrypted one. Instead delete each item that isn't a part of the encrypted data. (And don't use the form delete item 1 to -2 as it will delete all but the data after the last delimiter in the encrypted data). What will be left is just the encrypted part. 

2: Run 2 or more queries, one for non encrypted data, and more for the encrypted data. 

3: Return your data as an array or cursor. 

Hope I have helped someone avoid this pitfall. 

Bob