Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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.

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.


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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

The really strange introduction Once upon a time there were individuals who intentionally put the grass seeds to the soil with anticipation of solving their nutrition problems. Or they maybe only played with seeds and noticed what happened... Som…
Sequence is something that used to store data in it in very simple words. Let us just create a list first. To create a list first of all we need to give a name to our list which I have taken as “COURSE” followed by equals sign and finally enclosed …
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
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 …

604 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