Escaping characters while inserting from a python program.

Escaping characters while inserting from a python program.

Postby pidlas » Mon Apr 22, 2013 8:58 am

Hello,

I have been stuck for the past week on a problem resulting in various error messages according to the different solutions I could think of or tried to implement by searching the web.
Here is the python code :
Code: Select all
from os import chdir
import MySQLdb as mdb
import sys

# variables pour la BDD
nom_serveur = ''
etat = ''
regle = ''
directive = ''
logfile = []

# répertoire où se trouve les logs
chdir("c:/Users/pidlas/Documents/rudder/")

def extract(log):
    for ligne in log:
        debut_date = ligne.find('[')
        fin_date = ligne.find(']')
        date = ligne[debut_date+1:fin_date-5]
        sep_N = ligne.find('N:')
        nom_serveur = ligne[ligne.find('[',sep_N)+1:ligne.find(']',sep_N)]
        sep_S = ligne.find('S:')
        etat = ligne[ligne.find('[',sep_S)+1:ligne.find(']',sep_S)]
        sep_R = ligne.find('R:')
        regle = ligne[ligne.find('[',sep_R)+1:ligne.find(']',sep_R)]
        sep_D = ligne.find('D:')
        directive = ligne[ligne.find('[',sep_D)+1:ligne.find(']',sep_D)]
        logfile.append([date, nom_serveur, etat, regle, directive])

        try:
            db = mdb.connect('localhost', 'root', '', 'rudder');
            cursor = db.cursor()
            query = """INSERT IGNORE INTO serveur (nom_serveur) VALUES (%s)""" % (nom_serveur)
            cursor.execute(query)

        except mdb.Error, e:
            print "Error %d: %s" % (e.args[0],e.args[1])
            sys.exit(1)
           
    info.close()
    db.close()
    return logfile

if __name__ == "__main__":
    with open('non-compliant-reports.log','r') as info:
        extract(info)


Here is the error message I have with this programming :
Error 1054: Unknown column 'git.in.ac' in 'field list'


The name of the server I am trying to insert into my table is : <string.string.string-string.string>
It looks like the hyphen character is creating a problem to MySQLdb.

Anyone could give me a better understanding and eventually a solution as there must be one. ;)

Thanks in advance.
pidlas
 
Posts: 4
Joined: Mon Apr 22, 2013 8:45 am

Re: Escaping characters while inserting from a python progra

Postby setrofim » Mon Apr 22, 2013 9:05 am

Never do this:
Code: Select all
            query = """INSERT IGNORE INTO serveur (nom_serveur) VALUES (%s)""" % (nom_serveur)
            cursor.execute(query)

It's a massive security hole (and can also cause problems like the one you're observing). Instead, do this:
Code: Select all
            query = """INSERT IGNORE INTO serveur (nom_serveur) VALUES (%s)"""
            cursor.execute(query, (nom_serveur,))

This way, the execute method will handle the correct escaping of values for you. Also, note the comma in "(nom_serveur,)".
setrofim
 
Posts: 285
Joined: Mon Mar 04, 2013 7:52 pm

Re: Escaping characters while inserting from a python progra

Postby pidlas » Mon Apr 22, 2013 9:29 am

Merci :)
Il n'y a plus de messages d'erreur.
Cependant, l'insert n'a pas fonctionné. Ma table serveur est vide. :(
Une idée ?

Cordialement
pidlas
 
Posts: 4
Joined: Mon Apr 22, 2013 8:45 am

Re: Escaping characters while inserting from a python progra

Postby setrofim » Mon Apr 22, 2013 9:46 am

  1. Please post in English. This is an English language forum and most people here won't speak French (I don't).
  2. "There are more error messages" doesn't tell us much. Post the exact errors you're getting (with traceback).
setrofim
 
Posts: 285
Joined: Mon Mar 04, 2013 7:52 pm

Re: Escaping characters while inserting from a python progra

Postby pidlas » Mon Apr 22, 2013 10:03 am

Sorry for that.

Thanks. :) No more error messages.
But INSERT statement did not insert any datas into my table. :cry:
Any ideas ?
pidlas
 
Posts: 4
Joined: Mon Apr 22, 2013 8:45 am

Re: Escaping characters while inserting from a python progra

Postby setrofim » Mon Apr 22, 2013 10:05 am

Make sure you commit() before you close the connection.
setrofim
 
Posts: 285
Joined: Mon Mar 04, 2013 7:52 pm

Re: Escaping characters while inserting from a python progra

Postby pidlas » Mon Apr 22, 2013 11:23 am

Thank you so much. :D
That is all I needed.
Would you be kind enough to give any known tutorial on the use of mysqldb ?
pidlas
 
Posts: 4
Joined: Mon Apr 22, 2013 8:45 am

Re: Escaping characters while inserting from a python progra

Postby setrofim » Mon Apr 22, 2013 11:53 am

The closes thing to a tutorial would probably be the user guide. You may also want to read through PEP249 which documents the standard Python DB API (which MySQLdb documents).
setrofim
 
Posts: 285
Joined: Mon Mar 04, 2013 7:52 pm


Return to Web Development

Who is online

Users browsing this forum: No registered users and 2 guests

cron