[Webinar] Streamline your web hosting managementRegister Today


Python and MySQL

Posted on 2004-11-02
Medium Priority
Last Modified: 2007-12-19
   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!
Question by:petepalmer
LVL 24

Expert Comment

ID: 12474863
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.


Author Comment

ID: 12475073
   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?


LVL 24

Expert Comment

ID: 12480559
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.

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Expert Comment

ID: 12529295
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.
LVL 11

Expert Comment

ID: 12687211
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!

Expert Comment

ID: 12733383
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.


Expert Comment

ID: 12758855

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) )


Expert Comment

ID: 12783155
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!

Expert Comment

ID: 12786096
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.

Accepted Solution

OderWat earned 2000 total points
ID: 12786528
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.

Expert Comment

ID: 13428995
I think OderWat comment is the way to go, encode your data in Base64
LVL 15

Expert Comment

ID: 13713907
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) )


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Plenty of writing has gone on the web trying to compare Python with other competitive programming languages and vice versa. However, not much has been put into a wholistic perspective. This article should help you decide whether to adopt Python as a…
The purpose of this article is to demonstrate how we can upgrade Python from version 2.7.6 to Python 2.7.10 on the Linux Mint operating system. I am using an Oracle Virtual Box where I have installed Linux Mint operating system version 17.2. Once yo…
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question