Solved

Python and MySQL

Posted on 2004-11-02
6,081 Views
Last Modified: 2007-12-19
Hi,
   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!
0
Question by:petepalmer
    12 Comments
     
    LVL 24

    Expert Comment

    by:fridom
    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.

    Regards
    Friedrich
    0
     
    LVL 1

    Author Comment

    by:petepalmer
    Hi,
       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?

    Cheers,

    Pete
    0
     
    LVL 24

    Expert Comment

    by:fridom
    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.

    Regards
    Friedrich
    0
     
    LVL 3

    Expert Comment

    by:nochkin
    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

    message=re.sub("'","''",message)

    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.
    0
     
    LVL 11

    Expert Comment

    by:pratap_r
    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!
    Pratap
    0
     

    Expert Comment

    by:benkea01
    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.

    0
     
    LVL 1

    Expert Comment

    by:nickwong

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

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


    0
     
    LVL 1

    Expert Comment

    by:OderWat
    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

    myesc=MySQLdb.escape_string

    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!
    0
     

    Expert Comment

    by:sf2k
    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.
    0
     
    LVL 1

    Accepted Solution

    by:
    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.
    0
     
    LVL 1

    Expert Comment

    by:Whale
    I think OderWat comment is the way to go, encode your data in Base64
    0
     
    LVL 15

    Expert Comment

    by:mish33
    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)
    as VARCHAR  CHARACTER SET utf8.
    After that you can insert unicode strings without getting encoding errors.

    And as nickwong said above use

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


    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Variable is a place holder or reserved memory locations to store any value. Which means whenever we create a variable, indirectly we are reserving some space in the memory. The interpreter assigns or allocates some space in the memory based on the d…
    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 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 …
    Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…

    846 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now