Python and MySQL

   I've written a cool little script that helps to filter my email. It works really well and does exactly what I want.

   I added another feature that would store all the email in a database. This way I could easily release emails that I had accidentally blocked etc. Again this side of things worked fine but...

   I'm not sure how to properly escape the data I want to put into the database. In this case I'm using MySQL. The problem is in some emails there are characters that cause the insert to fail. This error then gets sent back to the person who sent me the email.

  The code I'm using to do the insert is :

  cursor.execute("insert into stored_email (efrom,subject,tstamp,completemail,status,logmessage) values ('" + afrom + "','" + asub + "',now(),'" + message.as_string() + "','" + status + "',' " + logmess +"')")

  The contents of the email is returned by message.as_string(). I guess what I need is a method that will safely go through the string that is returned and properly escape everything safely for MySQL.

   Hope someone can help me! :)

Thanks in advance!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Check mysql.escape_string

I like to warn you. Do not put some kind of input into a Database without checks. You are missing quite a few problems.
- you do not check for special characters which do have a meaning in SQL
- you do not check any length restriction.

I just can recommend to not the code as written. Sooner or later trouble will araise.

petepalmerAuthor Commented:
   Wouldn't  escape_string()  escape those dangerous characters ? If so that would negate the first problem you've raised. As for the second one, an email could quite easily have a large attachment. I have no idea in advance how big an email is going to be so it would be quite difficult to put a restriction on this I'd have thought.

  What do you think?


Well it probably should work. But I would test to see what *really* happens. And it might be intersting to check what the escaped string looks like.

Well the mail-content have to fit the spade the Database provided for it. Of course you could make that so large that it would fit, but you have to check how many data there are really. You then could e.g. just cut them at some point.

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

I don't know how do you process your message before inserting, but you might be just missing filter apostrophie sign.
For example, you can do the following:
If your message in message variable:
import re


That will substitite single ' to double '' which will not work as delimeter in mysql query.
You might want to check more deeply to see if there are any other characters might break your nice script.
mysql.escape_string should work fine, you dont need to sanitize further.

nochkin, imho using regex for this task might be overkill, a simple string replace would do. btw, the escape sequence should be \'

Have Fun!
You might actually want to use the prepared statement form to insert, it should escape all your strings and in addition prevent SQL injection attacks that might come in through email. for example

(String substitution)
result=cursor.execute("select * from table where id=%s"%"1 or id=2")

-> returns 2 results from the table

(Prepared statement form takes a tuple of values for the parameters.)
cursor.execute("select * from table where id=%s",("1 or 2"))

-> returns 1 result from the result for id = 1.  I believe this only works because of how conversion from string to int works in this particular case, for a string field this will not return any results except for exact matches.


As benkea01 said above, MySQLdb supports placeholders which you can use to automatically escape values as required.
So your execute statement would be:

    INSERT INTO stored_email (efrom,subject,tstamp,completemail,status,logmessage)
    VALUES ( %s, %s, now(), %s,  %s, %s )
    """, (afrom, asub, message.as_string(), status, logmess) )

Just write it as

cursor.execute("insert into stored_email (efrom,subject,tstamp,completemail,status,logmessage) values ('" + MySQLdb.escape_string(afrom) + "','" + MySQLdb.escape_string(asub) + "',now(),'" + MySQLdb.escape_string(message.as_string()) + "','" + MySQLdb.escape_string(status) + "',' " + MySQLdb.escape_string(logmess) +"')")

and it will work! Its not perfect code..

You can skip that if you are 100% sure that nobody can forge a ' into your data.

I would do something like


and use this in all places to make ist easier to read.. but thats me.

Pepeared statements are an option but I prefer to not "use the magic" in such places.. again thats me.

happy coding!
Just an idea, but if you converted the strings to UTF-8 instead of the standard ISO mime header text in the email, wouldn't the escaping become unnecessary?  Python would see the character as Unicode but I don't know if MySQL will see it as such or as a special character.  MySQL does have unicode support though.
He could convert the E-Mail in different way to avoid the ' chars (e. g. Base64) but it is of high importance to understand what escaping is and why and where it is needed. If he had no problems in the past he would probably not notice these constrains and the first time somebody sends an email containing something like '; DELETE .... it would harm his database.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I think OderWat comment is the way to go, encode your data in Base64
From version 4.1 MySQL supports utf8 character sets for storing Unicode data.
To store email text in unicode declare completemail (and possibly efrom and subject)
After that you can insert unicode strings without getting encoding errors.

And as nickwong said above use

    INSERT INTO stored_email (efrom, subject, tstamp, completemail, status, logmessage)
    VALUES ( %s, %s, now(), %s,  %s, %s )
    """, (afrom, asub, message.as_string(), status, logmess) )

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.