CSE 115 Introduction to Computer Science I Road map Review HTML - - PowerPoint PPT Presentation

CSE 115

Introduction to Computer Science I

Road map

▶︎ Review ◀ HTML injection SQL injection

Persisting data

Central Processing Unit

CPU

Random Access Memory

RAM

persistent storage (e.g. file or database)

Persisting data

text file - stream of characters CSV file - fields separated by comma database - can support highly efficient operations on data

SQLite

import sqlite3 conn = sqlite3.connect('atest.db') cur = conn.cursor() do things to database conn.commit() conn.close()

Changes to add_song

def add_song(song): #{"song_id": song_id, "title": title, "artist": artist} if songNotValid(song) or songIDAlreadyExists(song['song_id']) return with open(songs_filename, "a") as file: writer = csv.writer(file) writer.writerow([song['song_id'], song['title'], song['artist']]) def insertSong(song): cur.execute('INSERT INTO songs VALUES (?,?,?)', (song['song_id'], song['title'], song['artist']))

Now we can migrate from CSV to SQLite

Changes to add_song

def add_song(song): #{"song_id": song_id, "title": title, "artist": artist} if songNotValid(song) or songIDAlreadyExists(song['song_id']) return insertSong(song) conn.commit() def insertSong(song): cur.execute('INSERT INTO songs VALUES (?,?,?)', (song['song_id'], song['title'], song['artist']))

Define insertSong function to construct SQL command.

Changes to ratings.py

def rate_song(song_rating): #{"song_id": song_id, "rating": rating} if ratingNotValid(rating) or not songIDAlreadyExists(rating['song_id']): return with open(ratings_filename, "a") as file: writer = csv.writer(file) writer.writerow([song_rating['song_id'], song_rating['rating']])

Migrate from CSV to SQLite

Changes to ratings.py

def rate_song(song_rating): #{"song_id": song_id, "rating": rating} if ratingNotValid(rating) or not songIDAlreadyExists(rating['song_id']): return insertRating(rating) conn.commit() def insertRating(rating): cur.execute('INSERT INTO ratings VALUES (?,?)', (rating['song_id'], rating['rating']))

Migrate from CSV to SQLite

Road map

Review ▶︎ HTML injection ◀ SQL injection

HTML injection

User can type any text into a text field. If that text is incorporated into the HTML rendered by the browser, then a user could 'inject' HTML. In the application, see what happens when this text is entered as both an "Unsafe review" and a "Safe review":

<b>Some bold text</b>

HTML injection

User can type any text into a text field. What about this scary but in effect harmless HTML:

<button onclick="alert('You\'ve been hacked!!');">Click This</button>

HTML injection

User can type any text into a text field. How about this more nefarious HTML?

<!--

HTML injection

User can type any text into a text field. Or some HTML which makes the browser redirect to a different site.

HTML injection

User can type any text into a text field. Or this HTML which makes the browser redirect to a different site:

<META HTTP-EQUIV="refresh" CONTENT="1;url=http://www.buffalo.edu">

HTML injection prevention

User can type any text into a text field. Don't incorporate directly. Use an HTML escape mechanism which allows us to distinguish data from program. Characters like < > & " are encoded as &lt; &gt; &amp; &quot;

http://doc.locomotivecms.com/making-blog/2-6-html-escaping

HTML injection prevention

In our ratings.py code: re = rating['review'] re = html.escape(re)

Road map

Review HTML injection ▶︎ SQL injection ◀

SQL injection

User can type any text into a text field. The application has a search feature that allows a user to retrieve all the songs by a particular artist. Suppose the user enters

Boston

in the search box - what do we expect to see?

SQL injection

User can type any text into a text field. The application has a search feature that allows a user to retrieve all the songs by a particular artist. Suppose the user enters

Boston

in the search box - we'll see a listing of the songs by the artist Boston:

More than a feeling - Boston Something about you - Boston

SQL injection

User can type any text into a text field. If that text is incorporated into the SQL executed by our database engine, bad things can happen.

' OR '1'='1' --

• - starts a comment in SQL

causes rest of SQL in command to be ignored

SQL injection

The SQL command DROP TABLE someName removes the table whose name is someName, as in DROP TABLE songs

• r

DROP TABLE ratings

SQL injection

What would this do if we typed it into our search field?

Boston'; DROP TABLE songs; --

; separates commands in SQL

SQL injection

It turns out nothing, because the execute function does not permit multiple commands.

https://xkcd.com/327/

SQL injection prevention

with safe substitution

As a function

def insert(title, director, year): cur.execute('INSERT INTO movies VALUES (?,?,?)', (title, director, year))

'?' is a placeholder that is used for safe replacement: parameter substitution. Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see https://xkcd.com/327/ for humorous example of what can go wrong). Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. https://docs.python.org/3/library/sqlite3.html