<div dir="ltr"><span style="font-family:arial,sans-serif;font-size:13px">Ok, new code using ?:</span><div style="font-family:arial,sans-serif;font-size:13px"><br></div><div style="font-family:arial,sans-serif;font-size:13px"><div>import sqlite3</div><div><br></div><div>db = sqlite3.connect('db.sqlite')</div><div><br></div><div><br></div><div>def create_db():</div><div> db.execute('''</div><div> <span style="white-space:pre-wrap"> </span>CREATE TABLE TOPICS(</div><div> <span style="white-space:pre-wrap"> </span>ID INT PRIMARY KEY NOT NULL,</div><div> <span style="white-space:pre-wrap"> </span>URL VARCHAR NOT NULL,</div><div> <span style="white-space:pre-wrap"> </span>AUTHOR VARCHAR NOT NULL,</div><div> <span style="white-space:pre-wrap"> </span>MESSAGE VARCHAR NOT NULL</div><div> <span style="white-space:pre-wrap"> </span>);</div><div> <span style="white-space:pre-wrap"> </span>''')</div><div><br></div><div><br></div><div>def insert_db(_id, url, author, message):</div><div> db.execute("INSERT INTO TOPICS (ID, URL, AUTHOR, MESSAGE) VALUES (?, ?, ?, ?)", (_id, url, author, message))</div><div> db.commit()</div><div><br></div><div><br></div><div>def get_db(_id):</div><div><span style="white-space:pre-wrap"> </span>cursor = db.execute("SELECT ID, URL, AUTHOR, MESSAGE FROM TOPICS WHERE ID = ?", (_id))</div><div><span style="white-space:pre-wrap"> </span>return cursor.fetchone()</div><div><br></div><div><br></div><div>if __name__ == '__main__':</div><div><span style="white-space:pre-wrap"> </span>create_db()</div><div><span style="white-space:pre-wrap"> </span>insert_db(12, '<a href="http://abc.com/" target="_blank">abc.com</a>', 'a', 'b')</div><div><span style="white-space:pre-wrap"> </span>print(get_db(12))</div><div><span style="white-space:pre-wrap"> </span>db.close()</div></div><div style="font-family:arial,sans-serif;font-size:13px"><br></div><div style="font-family:arial,sans-serif;font-size:13px">-------------</div><div style="font-family:arial,sans-serif;font-size:13px"><br></div><div style="font-family:arial,sans-serif;font-size:13px">But now when I execute I get </div><div style="font-family:arial,sans-serif;font-size:13px"><br></div><div style="font-family:arial,sans-serif;font-size:13px"><div>Traceback (most recent call last):</div><div> File ".\sql.py", line 30, in <module></div><div> print(get_db(12))</div><div> File ".\sql.py", line 23, in get_db</div><div> cursor = db.execute("SELECT ID, URL, AUTHOR, MESSAGE FROM TOPICS WHERE ID = ?", (_id))</div><div>ValueError: parameters are of unsupported type</div></div><div style="font-family:arial,sans-serif;font-size:13px"><br></div><div style="font-family:arial,sans-serif;font-size:13px">-------------</div><div style="font-family:arial,sans-serif;font-size:13px"><br></div><div style="font-family:arial,sans-serif;font-size:13px">And the second time, again, I get</div><div style="font-family:arial,sans-serif;font-size:13px"><br></div><div style="font-family:arial,sans-serif;font-size:13px"><div>Traceback (most recent call last):</div><div> File ".\sql.py", line 28, in <module></div><div> create_db()</div><div> File ".\sql.py", line 14, in create_db</div><div> ''')</div><div>sqlite3.OperationalError: table TOPICS already exists</div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Oct 20, 2014 at 4:08 PM, Danny Yoo <span dir="ltr"><<a href="mailto:dyoo@hashcollision.org" target="_blank">dyoo@hashcollision.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">>>>> insert_db(12, "<a href="http://abc.com" target="_blank">abc.com</a>", "author", "message")<br>
> INSERT INTO TOPICS (ID, URL, AUTHOR, MESSAGE) VALUES (12, <a href="http://abc.com" target="_blank">abc.com</a>,<br>
> author, message)<br>
>>>><br>
><br>
> I've never used format like that. It looks like you need to quote the<br>
> strings. I don't know if you can tell format to do that or if you<br>
> have to escape them.<br>
<br>
<br>
</span>In normal situations, this might be good advice. When the string<br>
being produced is itself to be interpreted as code, though, we want to<br>
see if there's already some library to do the templating and quoting<br>
for us already. Otherwise, it is extraordinarily easy to leave an<br>
"injection attack" vulnerability.<br>
<br>
It doesn't even have to be one with malicious intent. See the<br>
following from way back in 2005:<br>
<br>
<a href="https://mail.python.org/pipermail/tutor/2005-June/039213.html" target="_blank">https://mail.python.org/pipermail/tutor/2005-June/039213.html</a><br>
<br>
In this case, getting it wrong just means that certain good inputs are<br>
treated incorrectly. So there's good reason to do this just so that<br>
our programs work.<br>
<br>
This is one of those issues that a programmer has to be aware of, to<br>
treat data with the proper respect and wariness. "Code is data, and<br>
data is code," is one of the mantras that the Lisp programmers use.<br>
When data becomes code, that's when we have to be especially careful.<br>
<div class="HOEnZb"><div class="h5">_______________________________________________<br>
Tutor maillist - <a href="mailto:Tutor@python.org">Tutor@python.org</a><br>
To unsubscribe or change subscription options:<br>
<a href="https://mail.python.org/mailman/listinfo/tutor" target="_blank">https://mail.python.org/mailman/listinfo/tutor</a><br>
</div></div></blockquote></div><br></div>