[Tutor] Python sqlite3 issue

[Juan Christian]

Ok, new code using ?:

import sqlite3

db = sqlite3.connect('db.sqlite')


def create_db():
    db.execute('''
     CREATE TABLE TOPICS(
     ID INT PRIMARY KEY NOT NULL,
     URL VARCHAR NOT NULL,
     AUTHOR VARCHAR NOT NULL,
     MESSAGE VARCHAR NOT NULL
     );
     ''')


def insert_db(_id, url, author, message):
    db.execute("INSERT INTO TOPICS (ID, URL, AUTHOR, MESSAGE) VALUES (?, ?,
?, ?)", (_id, url, author, message))
    db.commit()


def get_db(_id):
cursor = db.execute("SELECT ID, URL, AUTHOR, MESSAGE FROM TOPICS WHERE ID =
?", (_id))
return cursor.fetchone()


if __name__ == '__main__':
create_db()
insert_db(12, 'abc.com', 'a', 'b')
print(get_db(12))
db.close()

-------------

But now when I execute I get

Traceback (most recent call last):
  File ".\sql.py", line 30, in <module>
    print(get_db(12))
  File ".\sql.py", line 23, in get_db
    cursor = db.execute("SELECT ID, URL, AUTHOR, MESSAGE FROM TOPICS WHERE
ID = ?", (_id))
ValueError: parameters are of unsupported type

-------------

And the second time, again, I get

Traceback (most recent call last):
  File ".\sql.py", line 28, in <module>
    create_db()
  File ".\sql.py", line 14, in create_db
    ''')
sqlite3.OperationalError: table TOPICS already exists

On Mon, Oct 20, 2014 at 4:08 PM, Danny Yoo <dyoo at hashcollision.org> wrote:

> >>>> insert_db(12, "abc.com", "author", "message")
> > INSERT INTO TOPICS (ID, URL, AUTHOR, MESSAGE) VALUES (12, abc.com,
> > author, message)
> >>>>
> >
> > I've never used format like that.  It looks like you need to quote the
> > strings.  I don't know if you can tell format to do that or if you
> > have to escape them.
>
>
> In normal situations, this might be good advice.  When the string
> being produced is itself to be interpreted as code, though, we want to
> see if there's already some library to do the templating and quoting
> for us already.  Otherwise, it is extraordinarily easy to leave an
> "injection attack" vulnerability.
>
> It doesn't even have to be one with malicious intent.  See the
> following from way back in 2005:
>
>     https://mail.python.org/pipermail/tutor/2005-June/039213.html
>
> In this case, getting it wrong just means that certain good inputs are
> treated incorrectly.  So there's good reason to do this just so that
> our programs work.
>
> This is one of those issues that a programmer has to be aware of, to
> treat data with the proper respect and wariness.  "Code is data, and
> data is code," is one of the mantras that the Lisp programmers use.
> When data becomes code, that's when we have to be especially careful.
> _______________________________________________
> Tutor maillist  -  Tutor at python.org
> To unsubscribe or change subscription options:
> https://mail.python.org/mailman/listinfo/tutor
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/tutor/attachments/20141020/ffefdd4c/attachment-0001.html>