inserting into sqlite db-python

Question

I'm trying to use a SQLite database in my newest python program. I'm trying to insert a new row into my database by using insert. The database's columns are set up as:

0|pw|TEXT|0||0
1|Name|TEXT|0||0
2|dataBaseID|INT|0||0
3|email|TEXT|0||0 

My python code is:

this = "INSERT INTO users (pw, name, dataBaseID, email)"
this += " VALUES ("+passWord+", "+UserName+", "+"1"+", "+email+")"
print(this)
cur.execute(this)
dbCon.commit()

And the error is:

Traceback (most recent call last):
 File "/usr/lib/python3.2/multiprocessing/process.py", line 267, in _bootstrap
 self.run()
 File "/usr/lib/python3.2/multiprocessing/process.py", line 116, in run
 self._target(*self._args, **self._kwargs)
 File "serverBackend.py", line 85, in main
 add(msg,cur,databaseConnection,connect)
 File "serverBackend.py", line 20, in add
 cur.execute(this)
sqlite3.OperationalError: no such column: Chips1994

Why Is it throwing an error and indicating that the Im trying to access a column that doesn't exist? Thanks in advance.

• tjati: What is the result of print(this)?
• rady: INSERT INTO users (pw, name, dataBaseID, email) VALUES (Chips1994, raddingy, 1, raddingy_msu.edu)

Answer 1

You have to surround your values with " or something similar. What you're currently doing is in addition, really bad code and may result in some serious sql injection problems.

As stated in the official python-sqlite 3 documentation, you should use placeholders.

Replace your current code with

query = "INSERT INTO users (pw, name, dataBaseID, email) VALUES (?, ?, ?, ?)"
cur.execute(query, (passWord, UserName, 1, email))
cur.commit()

Using the placeholder ?, the sql wrapper will take care to surround the values with necessary characters like ".

Bonus tip: It seems, that you want to store a password in clear text. You should avoid this and use some hash method like PBKDF2.

• rady: Thanks! I completely forgot about that! I'm still in the process of learning sqlite.