We help IT Professionals succeed at work.

NameError: global name 'mySqlAesEncrypt' is not defined

Dolamite Jenkins
on
Does any one know where I can find some information on using mysql AED_ENCRYPT/DE ... Im looking for something that shows me the python syntax
so far I have used AES_ENCRYPT and got a name error and mySqlAesEncrypt also gave me name error ... I can't find anything that adresses python syntax ... is there a package I need to import and also what is the syntax for encrptyand decrypt... Thanks

key="special"
		db = MySQLdb.connect(host="169.109.459.12", user="Remote_user", passwd="",db="SEMSTRANSFER")
		cursor = db.cursor()
		cursor.execute("REPLACE INTO ptrecords (Actual_Incident_number,Incident_number, Last_Name, First_Name,Age,Gender,Address,City,State,Zip,Ailment,Treatment,Patient_reprt,Initial_contact,Hospital,Destination,Inservice,Provider_1,Provider_2,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp,Refusal,image) VALUES (%s,%s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s,%s, %s,%s, %s,%s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s)",(Actual_Incident_number,Incident_number, (mySqlAesEncrypt(Last_Name,key)),First_Name,Age,Gender,Address,City,State,Zip,Ailment,Treatment,Patient_reprt,Initial_contact,Hospital,Destination,Inservice,Provider_1,Provider_2,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp,Refusal,image));
		
		db.commit()
		cursor.close()
		db.close()

Open in new window

Comment
Watch Question

Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
Why not use the built-in AES_ENCRYPT in MySQL instead of relying on Python?
cursor.execute("REPLACE INTO ptrecords (Actual_Incident_number,
  Incident_number, Last_Name, First_Name, Age, Gender, Address,
  City, State, Zip, Ailment, Treatment, Patient_reprt, Initial_contact,
  Hospital, Destination, Inservice, Provider_1, Provider_2, Sys, Dia,
  Pulse, Resp, Weather, Temp, Humid, Wind, TimeStamp, Refusal, image)
VALUES (%s,%s, AES_ENCRYPT(%s, %s), %s, %s,%s, %s, %s,%s, %s, %s,%s,
  %s, %s,%s,%s, %s,%s, %s,%s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s)",
(Actual_Incident_number, Incident_number, Last_Name, key, First_Name,
  Age, Gender, Address, City, State, Zip, Ailment, Treatment,
  Patient_reprt, Initial_contact, Hospital, Destination, Inservice,
  Provider_1, Provider_2, Sys, Dia, Pulse, Resp, Weather, Temp, Humid,
  Wind, TimeStamp, Refusal, image));

Open in new window

Author

Commented:
thats what I am trying to do ....
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
Excellent.  Did you see line 6 in my example?  You want to call AES_ENCRYPT as part of the REPLACE statement.  I added in an extra placeholder because you want to send 'Last_Name' and 'key' as separate values to MySQL and let it do the encryption.

Author

Commented:
Thanks

Author

Commented:
Next question what is the syntax for DE_encrypting

db = MySQLdb.connect(host="localhost", user="root", passwd="",db="SEMSTRANSFER")
	    cursor = db.cursor()
	    cursor.execute('select * from ptrecords')

Open in new window

Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
Something like this for line 3:
cursor.execute("
  SELECT AES_DECRYPT(Last_name, %s) AS Last_name_decrypted, ptrecords.*
  FROM ptrecords
  WHERE Actual_Incident_number = %s",
 (key, Actual_Incident_number));

Open in new window


I'm not sure what your key is (assuming the field 'Actual_Incident_number')

More info here:
  http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_aes-encrypt

Make sure to make your Last_name field big enough (a multiple of 16 higher than your longest Last_name, so if you have somebody in your database with a hyphenated name or similar and the longest is 40 chars, you want Last_name to be at least 48bytes.  64 bytes to be on the safe side.)

Author

Commented:
thanks

Author

Commented:
im looping through this database and placing everything in the larger master db so I cant us conditions ... just select*from my table ... how can I do that ?

 
 WHERE Actual_Incident_number = %s",
 (key, Actual_Incident_number));

Open in new window

Author

Commented:
what does this mean and how do I fix it ?

Traceback (most recent call last):
  File "C:\Python26\sign\MYSQL_NEW_SERVER_SIDE._II.py", line 7022, in run
    Provider_1,Provider_2,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp,Refusal,image))
ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings.

Open in new window


key="special"
cursor.execute("SELECT AES_DECRYPT(Last_name, %s) AS Last_name_decrypted, ptrecords.* FROM ptrecords ", (key));

Open in new window

IT Supervisor
Top Expert 2009
Commented:
The value of Last_name from AES_DECRYPT should be a standard 7-bit string, the encrypted version from "ptrecords.*" will be an 8-bit string, which is causing the problem you see here.  The simplest thing to do would be to expand out the entire query instead of using the glob (the "*" thingy):
cursur.execute("SELECT Actual_Incident_number,
  Incident_number, AES_DECRYPT(Last_Name, %s), First_Name, Age, Gender, Address,
  City, State, Zip, Ailment, Treatment, Patient_reprt, Initial_contact,
  Hospital, Destination, Inservice, Provider_1, Provider_2, Sys, Dia,
  Pulse, Resp, Weather, Temp, Humid, Wind, TimeStamp, Refusal, image
FROM ptrecords", key);

Open in new window

Author

Commented:
nemws1 it works and dycrypts but I still get this error

ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings.

Open in new window


Here is my entire code section

def run(self):
	try: 
	    db = MySQLdb.connect(host="localhost", user="root", passwd="",db="SEMSTRANSFER")
	    cursor = db.cursor()
	    key="special"
	    cursor.execute("SELECT Actual_Incident_number, Incident_number, AES_DECRYPT(Last_Name, %s), First_Name, Age, Gender, Address,  City, State, Zip, Ailment, Treatment, Patient_reprt, Initial_contact,Hospital, Destination, Inservice, Provider_1, Provider_2, Sys, Dia,Pulse, Resp, Weather, Temp, Humid, Wind, TimeStamp, Refusal, image FROM ptrecords", key);
	    for rows in cursor:
		Actual_Incident_number=rows[0]
		
		Incident_number = rows[1]
		
		Last_Name = rows[2]
		print Last_Name
		First_Name = rows[3]
		Age= (str(rows[4]))
		Gender = rows[5]
		Address = rows[6]
		City= rows[7]
		State = rows[8]
		Zip = (str(rows[9]))
		Ailment = rows[10]
		Treatment = rows[11]
		Patient_reprt= rows[12]
		Initial_contact= (str(rows[13]))
		Hospital = (str(rows[14]))
		Inservice= (str(rows[16]))
		Destination= (rows[15])
		Provider_1 =(rows[17])
		Provider_2 = (rows[18])
		Sys =(str(rows[19]))
		Dia=(str(rows[20]))
		Pulse=(str(rows[21]))
		Resp=(str(rows[22]))
		Weather=(str(rows[23]))
		Temp=(str(rows[24]))
		Humid=(str(rows[25]))
		Wind=(str(rows[26]))
		TimeStamp=(str(rows[27]))
		Refusal=rows[28]
		image =rows[29]
    
	   
		#LOAD  information into new database
		con = lite.connect('PermPatRecord.sqlite')
		cur = con.cursor()
		cur.execute('insert or replace into ptrecords values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
		    (Actual_Incident_number,Incident_number, Last_Name, First_Name, Age,Gender,Address,City,State,Zip
		     ,Ailment,Treatment,Patient_reprt,Initial_contact,Hospital,Destination,Inservice,
		     Provider_1,Provider_2,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp,Refusal,image))
		con.commit()
		cur.close()
		con.close()
	    db.commit()
	    cursor.close()
	    db.close()       
	    self.repopulate()
	    self.cleardatabase()
	    #os.remove('Temp.sqlite')
	except Exception as e:
	    traceback.print_exc(file=open("errlog.txt","a"))
	    exc_type, exc_value, exc_tb = sys.exc_info()
	    
	    db.rollback

Open in new window



and the entire error message

Traceback (most recent call last):
  File "C:\Python26\sign\MYSQL_NEW_SERVER_SIDE._II.py", line 7027, in run
    Provider_1,Provider_2,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp,Refusal,image))
ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings.

Open in new window

Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
I can't see anyplace where this error would occur.  Does line 7027 (from the error) correspond to line 49 in the code you posted?

Author

Commented:
I found the problem with sqlite for some reason on the connect you have to declare a row factory ..

Author

Commented:
thank you