cse 115
play

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

Sep 28, 2023 •332 likes •600 views

CSE 115 Introduction to Computer Science I Road map Review HTML injection SQL injection Persisting data Central Random Processing Access Unit Memory CPU RAM persistent storage (e.g. file or database) Persisting data text

  1. CSE 115 Introduction to Computer Science I

  2. Road map ▶︎ Review ◀ HTML injection SQL injection

  3. Persisting data Central Random Processing Access Unit Memory CPU RAM persistent storage (e.g. file or database)

  4. Persisting data text file - stream of characters CSV file - fields separated by comma database - can support highly e ffi cient operations on data

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

  6. 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

  7. 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.

  8. 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

  9. 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

  10. Road map Review ▶︎ HTML injection ◀ SQL injection

  11. 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>

  12. HTML injection User can type any text into a text field. What about this scary but in e ff ect harmless HTML: <button onclick="alert('You\'ve been hacked!!');">Click This</button>

  13. HTML injection User can type any text into a text field. How about this more nefarious HTML? <!--

  14. HTML injection User can type any text into a text field. Or some HTML which makes the browser redirect to a di ff erent site.

  15. HTML injection User can type any text into a text field. Or this HTML which makes the browser redirect to a di ff erent site: <META HTTP-EQUIV="refresh" CONTENT="1;url=http://www.buffalo.edu">

  16. 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

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

  18. Road map Review HTML injection ▶︎ SQL injection ◀

  19. 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?

  20. 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

  21. 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

  22. SQL injection The SQL command DROP TABLE someName removes the table whose name is someName , as in DROP TABLE songs or DROP TABLE ratings

  23. SQL injection What would this do if we typed it into our search field? Boston'; DROP TABLE songs; -- ; separates commands in SQL

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

  25. https://xkcd.com/327/

  26. 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

Recommend

More recommend