#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2009
    Posts
    41
    Rep Power
    0

    Syntax Error!?!? Python and sqlite3


    I am trying to learn how to use sqlite3 from python, but I keep getting a syntax error when trying to insert data into the database. I can't figure out why it wont work, my python and sql code looks valid, so i'm hoping someone can help me out.

    Code:
    for x in range(0,len(lemail)-2):
    	sql = "INSERT INTO Student (email, name) VALUES ('" + lemail[x] + "', '" + lname[x].rstrip() + "')"
    	sql = str(sql)
    	cursor.execute(sql)
    lemail and lname are lists with corresponding emails and names, for example lemail[0] = john.smith@email.com and lname[0] = smith, john.

    The error I get is:
    > python .\database.py
    Traceback (most recent call last):
    File ".\database.py", line 47, in <module>
    cursor.execute(sql)
    sqlite3.OperationalError: near "a": syntax error



    Also the table name and attributes are valid, I can run the same command manually to insert data into the database and it works flawlessly, I just get a 'syntax error' when I try to automate it. Any help is appreciated. Thanks in advance
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Feb 2004
    Location
    London, England
    Posts
    1,585
    Rep Power
    1378
    I'm guessing that one of the entries in lemail or lname has a quote character in it.

    Building up the string like that is a big no-no. Characters like quotes will screw it up, and this can be used by malicious people to inject code into your database.

    What you should do is something like this:
    Code:
    sql = "INSERT INTO Student (email, name) VALUES (?, ?)"
    cursor.execute(sql, (lemail[x], lname[x]))
    The values in the tuple (lemail[x], lname[x]) will be substituted in place of the "?" placeholders.

    This is safer, more efficient and easier to code than building up a string.

    Dave

    Comments on this post

    • lnxgeek agrees
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2009
    Posts
    41
    Rep Power
    0
    Originally Posted by DevCoach
    I'm guessing that one of the entries in lemail or lname has a quote character in it.

    Building up the string like that is a big no-no. Characters like quotes will screw it up, and this can be used by malicious people to inject code into your database.

    What you should do is something like this:
    Code:
    sql = "INSERT INTO Student (email, name) VALUES (?, ?)"
    cursor.execute(sql, (lemail[x], lname[x]))
    The values in the tuple (lemail[x], lname[x]) will be substituted in place of the "?" placeholders.

    This is safer, more efficient and easier to code than building up a string.

    Dave

    It works great now, thank you so much Dave!!!
  6. #4
  7. No Profile Picture
    I AM A GOLDEN GOD
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Apr 2003
    Location
    Camarillo, California
    Posts
    5,929
    Rep Power
    1171
    stringzar> Be sure to read up on parameterized queries and SQL injection!
    "Seriously, we're not a search engine, we're actual people." ~ ManiacDan

    BookMooch.com : Give books away. Get books you want.

IMN logo majestic logo threadwatch logo seochat tools logo