YouTube

Got a YouTube account?

New: enable viewer-created translations and captions on your YouTube channel!

English subtitles

← Bobby Tables Destroyer of Posts - Intro to Relational Databases

Get Embed Code
4 Languages

Showing Revision 4 created 05/25/2016 by Udacity Robot.

  1. Now in adapting our form DB
    code to use a real database,

  2. you might have written something
    that looks a lot like this.
  3. In the get all posts function, you
    connect to a database, make a cursor,
  4. execute a SELECT statement,
    format the results appropriately,
  5. close the connection return the posts.
  6. But then to add a post,
    connect to the database, make a cursor.
  7. Execute an INSERT that
    substitutes in the post content,
  8. commit that to the database and close.
  9. Now, this looks good,
    but it isn't quite.
  10. If you're writing a bunch
    of different forum app.
  11. Are there any posts that don't
    seem to work quite right?
  12. Say what?
  13. Wait a minute.
  14. That looked like a perfectly good post.
  15. Why are we getting this
    weird error from it?
  16. Let's go back to our terminal.
  17. Oh, look at this.
  18. We have a trace back from python.
  19. It says programming error.
  20. Syntax error at or near t.
  21. And there's out INSERT statement VALUES.
  22. I can't find a problem.
  23. Let's look back at the code.
  24. So, here's where the post content,
    gets sent to the database.
  25. It just gets added into a SQL statement.
  26. Inside single quotes.
  27. Because, we put SQL strings
    inside single quotes.
  28. But the database, sees the quote from
    the post, and it thinks that's the end
  29. of a string, and that t is
    something it doesn't understand.
  30. By the way,
    if your code didn't have this bug,
  31. congratulations, that's awesome.
  32. But stay tuned, because there's more
    to this bug than might first appear.
  33. Despite the fact that we had a little
    problem, we can still post things.
  34. As long as they don't have
    single quotes in them.
  35. But here's something to try.
  36. Single quote.
  37. Close param.
  38. Semi colon.
  39. Don't retype this from what I'm saying.
  40. Copy it from the instructor notes,
    and put them into your forum.
  41. Delete from posts.
  42. Semi colon.
  43. Double dash.
  44. Post this.
  45. Wait, all, all of our posts are gone.
  46. I thought we had a database here.
  47. Hey, wait a minute.
  48. I thought I saw this one
    on the webcomic XKCD.
  49. What we have here is a security hole
    called an SQL injection attack.
  50. Some of the post's text is being
    treated as a database command,
  51. namely delete from posts.
  52. Which as it happens, means delete
    every row from the posts table.
  53. Well, that stinks.
  54. All those brilliant test
    posts we wrote are gone, and
  55. we have a famous security
    bug in our code.
  56. How are we going to fix this?
  57. Well, we might not be able to get
    all those great posts back, but
  58. we should be able to at least
    keep it from happening again.