We help IT Professionals succeed at work.

How do I select an encrypted string from a mysql database?

I am inserting an AES encrypted string into a table using the AES_ENCRYPT() function.  
I can select the decrypted value using the AES_DECRYPT().  However, the query returns an empty result when I try to select the encrypted string.   I can view the encrypted string in MYSQL Workbench.  What kind of query should I use to return this string?
Comment
Watch Question

Nem SchlechtIT Supervisor
CERTIFIED EXPERT
Top Expert 2009

Commented:
It should just be:
SELECT AES_DECRYPT(field_name, 'key_value')
FROM table_name
;

Open in new window

If it decrypts incorrectly (ie: wrong key_value) you will get a NULL result.

You can check this by adding ISNULL() around the AES_DECRYPT call:
SELECT ISNULL(AES_DECRYPT(field_name, 'key_value'))
FROM table_name
;

Open in new window


If this returns 1, then you're NOT providing the correct key_value.

Author

Commented:
I am not trying to decrypt the field.  I am trying to copy the encrypted string to a buffer and pass it to the TextOut() function.





Author

Commented:
How to I determine the lenth for the encrypted string?

Author

Commented:
I figured it out.  16 * (trunc(string_length / 16) + 1)
Nem SchlechtIT Supervisor
CERTIFIED EXPERT
Top Expert 2009

Commented:
Ah, sorry for my confusion.  A simple SELECT should return the results, but you need to be careful with how you fetch it back, since it is an 8-bit binary value (as opposed to a 7-bit character string).

Can you post the code for what you're trying to do now that's not working?

Author

Commented:
Yeh!  How do I fetch binary data?  


void DisplayAccount(HWND hwnd, _BGCELL cell, char *account, char *title)
{
	MYSQL *mysql = NULL;
	MYSQL_RES *res = NULL;
	MYSQL_ROW row = NULL;
	char *db_name = "userpass";
	int len = 0;
	int ret = 0;
	int num = 1;
	char query[256] = {'\0'};

	SendMessage(hwnd,BGM_SETGRIDDIM,4,1);
	SendMessage(hwnd,BGM_SETCOLSNUMBERED,0,0);
	SendMessage(hwnd,BGM_SETROWSNUMBERED,0,0);
	SendMessage(hwnd,BGM_CLEARGRID,0,0);
	SendMessage(hwnd,BGM_SETCOLWIDTH,0,120);

                     //load header labels
	PutCell(hwnd,cell,0,1,1,title);
                     PutCell(hwnd,cell,1,0,1,"Account Name");
                     PutCell(hwnd,cell,2,0,1,"Username");
                     PutCell(hwnd,cell,3,0,1,"Password");
	PutCell(hwnd,cell,4,0,1,"Key");

	mysql = open_db(db_name);
	if(mysql){

		len = sprintf_s(query,256,"SELECT username, password FROM userpass WHERE account_name = '%s'",account);

		ret = mysql_real_query(mysql,query,len);
		if(ret){
			Message("DisplayAccount() could not select records %s",mysql_error(mysql));
		}

		res = mysql_use_result(mysql);
		row = mysql_fetch_row(res);
		if(row){
			PutCell(hwnd,cell,1,1,1,account);
			PutCell(hwnd,cell,2,1,1,row[0]);  // Depending on the characters they do not show up in the cell
			PutCell(hwnd,cell,3,1,1,row[1]);
		}

		SendMessage(hwnd,BGM_SETEDITABLE,TRUE,0);
		PutCell(hwnd,cell,4,1,1," ");
			
		mysql_free_result(res);
		mysql_close(mysql);
	}else{

	}

	HideWindows();
	SetWindowPos(hwnd,HWND_BOTTOM,0,0,hMainRect.right,hMainRect.bottom-230,SWP_SHOWWINDOW);
	ShowWindow(hUpdate,SW_NORMAL);
	ShowWindow(hDelete,SW_NORMAL);
	ShowWindow(hDecrypt,SW_NORMAL);
}

Open in new window

IT Supervisor
CERTIFIED EXPERT
Top Expert 2009
Commented:
Hmmm... well, I'll be honest and I say I'm not sure what the problem might be.  The password is most likely being correctly returned from the database and looking at your code, it should be stored correctly as well.  My guess is that PutCell() might be doing something odd with it or getting confused by the binary string.

I'm curious as to *why* you would want to look at what is basically binary garbage in your table.  Be that as it may, try changing your query to this (add a call to HEX()) and see if it is being displayed correctly (your line 28):

 
len = sprintf_s(query,256,"SELECT username, HEX(password) FROM userpass WHERE account_name = '%s'",account);

Open in new window


Which is just HEX encoding the binary string so that it's plain ASCII and readable.  If you need, you can always UNHEX() this back to its binary version.

Note that this will double the length of password -   32 * (trunc(string_length / 16) + 1)

Author

Commented:
I am just trying to learn how mysql works.  

The DrawText() function works as expected.
DrawText(hdc,(LPCSTR)row[0],19,&r,DT_SINGLELINE|DT_CENTER|DT_VCENTER);

PutCell() calls SendMessage() to set the cell data.  
SendMessage(hgrid,BGM_SETCELLDATA,(UINT)&cell,(long)text);
Here the text gets passed as a LPARAM, but I cannot read it on the other side. It only happens when there is a carriage return, line break or space in the data.  The following binary data does not get set.

 ;%å ù
³‰dvqš

Explore More ContentExplore courses, solutions, and other research materials related to this topic.