For a full reference to the Python DB-API, see the specification and the documentation for specific database modules, such as sqlite3 and psycopg2.
module.connect(...)
Connect to a database. The arguments to connect differ from module to module; see the documentation for details. connect returns a Connection object or raises an exception.
For the methods below, note that you don't literally call (for instance) Connection.cursor() in your code. You make a Connection object, save it in a variable (maybe called db) and then call db.cursor().
Connection.cursor()
Makes a Cursor object from a connection. Cursors are used to send SQL statements to the database and fetch results.
Connection.commit()
Commits changes made in the current connection. You must call commit before closing the connection if you want changes (such as inserts, updates, or deletes) to be saved. Uncommitted changes will be visible from your currect connection, but not from others.
Connection.rollback()
Rolls back (undoes) changes made in the current connection. You must roll back if you get an exception if you want to continue using the same connection.
Connection.close()
Closes the connection. Connections are always implicitly closed when your program exits, but it's a good idea to close them manually especially if your code might run in a loop.
Cursor.execute(statement)
Cursor.execute(statement, tuple)
Execute an SQL statement on the database. If you want to substitute variables into the SQL statement, use the second form — see the documentation for details.
If your statement doesn't make sense (like if it asks for a column that isn't there), or if it asks the database to do something it can't do (like delete a row of a table that is referenced by other tables' rows) you will get an exception.
Cursor.fetchall()
Fetch all the results from the current statement.
Cursor.fetchone()
Fetch just one result. Returns a tuple, or None if there are no results.
All files inside the vagrant directory are shared between your computer's OS and the virtual machine.
When you've brought the forum web server up (see instructions in the lesson), you can access it from your browser at http://localhost:8000/.
The psql command-line tool is really powerful. There's a complete reference to it in the PostgreSQL documentation.
To connect psql to a database running on the same machine (such as your VM), all you need to give it is the database name. For instance, the command psql forum will connect to the forum database.
From within psql, you can run any SQL statement using the tables in the connected database. Make sure to end SQL statements with a semicolon, which is not always required from Python.
You can also use a number of special psql commands to get information about the database and make configuration changes. The \d posts command shown in the video is one example — this displays the columns of the posts table.
Some other things you can do:
\dt — list all the tables in the database.
\dt+ — list tables plus additional information (notably, how big each table is on disk).
\H — switch between printing tables in plain text vs. HTML.
Read the documentation for Bleach here: http://bleach.readthedocs.org/en/latest/
The syntax of the update and delete statements:
update table set column = value where restriction ; delete from table where restriction ;
The where restriction in both statements works the same as in select and supports the same set of operators on column values. In both cases, if you leave off the where restriction, the update or deletion will apply to all rows in the table, which is usually not what you want.
The like operator supports a simple form of text pattern-matching. Whatever is on the left side of the operator (usually the name of a text column) will be matched against the pattern on the right. The pattern is an SQL text string (so it's in 'single quotes') and can use the % sign to match any sub-string, including the empty string.
If you are familiar with regular expressions, think of the % in like patterns as being like the regex .* (dot star).
If you are more familiar with filename patterns in the Unix shell or Windows command prompt, % here is a lot like * (star) in those systems.
For instance, for a table row where the column fish has the value 'salmon', all of these restrictions would be true:
And all of these would be false:
The term "spam" referring to junk posts comes from Monty Python's "Spam" sketch. On the Internet, "spamming" was first used to mean repetitious junk messages intended to disrupt a group chat. Later, it came to refer to unsolicited ads on forums or email; and more recently to more-or-less any repetitious or uninvited junk message.
# To see how the various functions in the DB-API work, take a look at this code,
# then the results that it prints when you press "Test Run".
#
# Then modify this code so that the student records are fetched in sorted order
# by student's name.
#
import sqlite3
# Fetch some student records from the database.
db = sqlite3.connect("students")
c = db.cursor()
query = "select name, id from students order by name;"
c.execute(query)
rows = c.fetchall()
# First, what data structure did we get?
print "Row data:"
print rows
# And let's loop over it too:
print
print "Student names:"
for row in rows:
print " ", row[0]
db.close()
------------------------------------
Row data:
[(u'Diane Paiwonski', 773217), (u'Harry Evans-Verres', 172342), (u'Hoban Washburne', 186753), (u'Jade Harley', 441304), (u'Jonathan Frisby', 917151), (u'Melpomene Murray', 102030), (u'Robert Oliver Howard', 124816), (u'Taylor Hebert', 654321), (u'Trevor Bruttenholm', 162636)]
Student names:
Diane Paiwonski
Harry Evans-Verres
Hoban Washburne
Jade Harley
Jonathan Frisby
Melpomene Murray
Robert Oliver Howard
Taylor Hebert
Trevor Bruttenholm
# This code attempts to insert a new row into the database, but doesn't
# commit the insertion. Add a commit call in the right place to make
# it work properly.
#
import sqlite3
db = sqlite3.connect("testdb")
c = db.cursor()
c.execute("insert into balloons values ('blue', 'water') ")
db.commit()
db.close()
Before:
+-------+----------+
| color | contents |
+=======+==========+
| red | air |
| green | helium |
+-------+----------+
After:
+-------+----------+
| color | contents |
+=======+==========+
| red | air |
| green | helium |
| blue | water |
+-------+----------+
In SQL, a double dash (--) signifies the end of the string. Adding a double dash to the end of your SQL injection basically makes anything after it a comment, thus making the webpage ignore it.
This is useful for making the server ignore the final quotation mark at the end of an SQL command. E.g. if the SQL looked like this:
SELECT * FROM users WHERE username=' $_POST['uname']';
(POST is the PHP command to get information from a form) entering the command above but with a double dash will solve this problem. The SQL statement would now look like this:
SELECT * FROM users WHERE username=' ' or 1=1--';
because of the double dash at the end, the '; gets ignored making the query valid and again 1 is always equal to 1 so it will select the first username in the database, which is usually the Admin.
The variables placeholder must always be a %s, even if a different placeholder (such as a %d for integers or %f for floats) may look more appropriate:
>>> cur.execute("INSERT INTO numbers VALUES (%d)", (42,)) # WRONG
>>> cur.execute("INSERT INTO numbers VALUES (%s)", (42,)) # correct
>>> cur.execute(
... """INSERT INTO some_table (an_int, a_date, a_string)
... VALUES (%s, %s, %s);""",
... (10, datetime.date(2005, 11, 18), "O'Reilly"))
is converted into the SQL command:
INSERT INTO some_table (an_int, a_date, a_string)
VALUES (10, '2005-11-18', 'O''Reilly');
Named arguments are supported too using %(name)s placeholders
>>> cur.execute(
... """INSERT INTO some_table (an_int, a_date, another_date, a_string)
... VALUES (%(int)s, %(date)s, %(date)s, %(str)s);""",
... {'int': 10, 'str': "O'Reilly", 'date': datetime.date(2005, 11, 18)})
Bleach is a whitelist-based HTML sanitization and text linkification library. It is designed to take untrusted user input with some HTML.
https://bleach.readthedocs.org/en/latest/
This is not the only right answer, but it's one possible approach.
Checking for unsafe content both when we receive a post, and when we display that post to a user, is the most careful approach.
However, removing bad stuff from posts when they are submitted means that we might lose our chance to catch someone in the act of sending an attack.
What's right for your service and your users depends on what you want to accomplish!
