DAVID MALAN: All right, this is CS50 and this is lecture 10 Welcome to those parents among you Today, for those tuning in online, is freshmen family weekend We have quite a few guests with us and we thought we would begin today in fact by perhaps putting everyone at ease since I know this is perhaps the first time in a while that you’ve been back to college Maybe this is your first class for the fall semester And so we thought we’d share with you by proxy what you might be feeling by way of a short film that CS50’s video team put together with a little friend of ours just a few semesters ago [VIDEO PLAYBACK] [MUSIC PLAYING] – Hi – Hello [END PLAYBACK] DAVID MALAN: All right, well, welcome to Harvard and CS50 So if you’d like to actually return in just a few weeks time, your children or siblings or friends or here in the room will be exhibiting their CS50 final projects in just a few weeks’ time This is the so-called CS50 fair, which is meant to be a campus wide exhibition of students’ final projects And it’s worth noting that, as the students in the class know, 68% of the students in CS50 this year had no prior CS experience And so what you and they will see on display at the CS fair in a few weeks’ time in December is all of their accomplishments And in fact, the goal of this event really is to delight in what everyone has achieved and glancing at each other’s screens and phones to see with great surprise what each of our students has pulled off So you are invited back if you would like for that as well Now CS50 of course is characterized as a class for those less comfortable and those more comfortable and those somewhere in between And I daresay some of our guests today are perhaps at one end of that spectrum or the other And so rest assured that what we’ll dive into today, the world of databases and SQL with applications to data science and beyond, is new to most everyone in the room as well So you are in good company, and I daresay 68% or more of percent of the parents in the room are perhaps feeling exactly as you are So if we can now turn our attention back to some things lower level, those students among you will recall that we focused in recent days on Python, this other higher level language by which you can solve problems a bit more readily than you can using, say, something lower level like C. Now you might have noticed I’m ready if you dived into problem set 6 as I’m sure you have, the debug 50 only supports C and not Python So you have relatively fewer tools at your disposal for debugging But do realize that within the CS50 library for Python is a function like this called eprint, which you might recall if you’ve used eprintf with C This is a Python version of the same And so if you need help debugging your programs, it suffices to write a program in Python And anywhere you want to see diagnostic output, simply call this eprint function

And it will print the file name and the line number for which you have written that line of code But let’s turn our attention now to things more web-oriented and perhaps something with which you’re familiar Facebook and the login screen thereof So there’s so many websites out there today where when you visit them, you’re prompted to log in– something like this But you’re not prompted to log in every time you click a link on facebook.com You’re not prompted to log in every minute or every two minutes because indeed, the websites, if they required as much, they would degrade into something downright unusable So instead, these websites remember that you’re logged in somehow So you log in once per day, per week, per year It varies by website And then somehow the website remembers that you are Maria or you are Brian and you have already logged into this website Now how might that work? How does Facebook or any site like it, Gmail and beyond, remember that you are logged in once you are logged in even though you only type your username and password once? AUDIENCE: [INAUDIBLE] DAVID MALAN: OK, so they store information on the server And indeed, that’s exactly right On the server, as we’re now starting to see with Python and today with SQL, you have the ability to store information on a server We saw very simply about a week ago the ability to store data in, like, CSV files, just text files on the server That’s not going to be very high performing, so we’re going to do better than that today But you can also store data in variables inside a flask while it’s running as we saw as well But we’ll need some more sophistication than that But yes, we can certainly remember that Maria or Brian have logged in But how do we remember from which computer or browser Maria or Brian have logged in? AUDIENCE: [INAUDIBLE] DAVID MALAN: Via the IP address– so could work You could just recall that from those virtual envelopes which I no longer have today We’ve been writing the to address in the from address, the IP address, on those envelopes So you could certainly remember, oh, I saw Brian from 1.2.3.4 and Maria from 4.5.6.7 or whenever the numbers actually are But that’s a problem in, like, households and frankly even on campuses these days because of NAT, Network Address Translation, that technique whereby a bunch of us probably in this room right now on our laptops or phones are sharing the same IP address, at least so far as the outside world is concerned So we could confuse things And that would be bad because that means if Brian sort of logs into Facebook and Maria logs into Facebook, suddenly Brian might actually see Maria’s profile page or the like So what else might they be doing? What’s that? AUDIENCE: Cookie? DAVID MALAN: Cookie, so– eh, correct answer, but what in the world does that mean? AUDIENCE: [INAUDIBLE] DAVID MALAN: Don’t know– OK So, perfect– so you’re in the right place And most everyone in this room probably, if you use the web frequently, have generally heard about cookies and probably that they’re dangerous or bad or some way or you’ve probably googled how to clear your cookies if you’ve indeed heard they’re a bad thing or something like that And indeed, cookies are the answer to this question But how does that actually work? Well, if you’ve been to like an amusement park or a club or someplace where you typically have a ticketed access gate, sometimes if you want to come in go to and from the parking lot or out to the street or wherever, they might stamp your hand using like a little red stamp or invisible stamp or the like And then you just show it to the bouncer or the person at the checker that you had been there before because you have this hand stamp via the remembering you So web servers and browsers are kind of the same When you visit Facebook or Gmail or wherever for the first time and moreover log in, they essentially put like a digital hand stamp on your browser called a cookie And that handstand happens to be implemented not obviously as ink but like as a big random number that is given just to you And it’s stored either in your computer’s RAM or on your computer’s hard drive longer term And your browser, because it speaks HTTP, that protocol we’ve been talking about, it has been designed to show its hand, so to speak Every time you revisit Facebook, every time you click an email, every time you send a message, anytime you browse around on that same website, the browser is, without you realizing it, reminding the server of that hand stamp So how might it be doing it? Where can a browser sort of tuck away information like this such that it’s always in that envelope? Yeah AUDIENCE: It can put it like into a list like you did before with the invites or something like that DAVID MALAN: The invites, the invites– oh, so that would be lower lev– so, OK, I remember now So that would be not quite there because we want to make sure that this has nothing to do fundamentally with our application This has to do with HTTP So this was invented years ago before there was like a list of registrants for [INAUDIBLE] So we can’t put it there Yeah AUDIENCE: [INAUDIBLE] DAVID MALAN: A concept of a cache– not here, no, no Yeah AUDIENCE: [INAUDIBLE] header DAVID MALAN: The header– so the header is where we have access to metadata, sort of lower level technical information that most of us never see unless you poke around Chrome or actually sniff your own internet traffic and see But we do have the ability, within these HTB headers, to send, so to speak, this virtual hand stamp So let’s recall what these headers look like So the headers that get sent in this virtual envelope when you request Facebook’s home page, for instance, might look like this– get slash, which means get me the home page, using version 1.1 The host I’m visiting, just as a reminder, is facebook.com or www.facebook.com

And then below that is maybe more headers We’ve generally waived our hands at it because they’re not as interesting but there might be some more information there too Well, when Facebook replies, especially after you’ve logged in, it turns out one of the headers we haven’t looked at in addition to these which we have seen, the response, the 200 OK from the server, is that Facebook and servers like it are probably sending this header as well, literally Set-Cookie colon and then a name and then a value or key and a value The name here, for instance, I’m going to start calling session, as is the term of art And then the value to the right can actually be anything So value might be 123, 456, or any unique value that’s sort of randomly generated by the server to remember who you are And so session equals value is like this is your hand and this is your value That is now the cookie that’s been planted on the computer And what your browser thereafter is supposed to do is not send set cookie That only comes from server to browser But whenever you on Facebook request another page, click a friend’s profile page or the like, your virtual envelope will contain not these familiar headers but also cookie session equals value And so that is the presentation of that same key value pair So if Facebook now to your point earlier remembers that user 123456 has logged in and that user happens to be named Maria and we can store that information on the server, every time Facebook sees this same hand stamp or sees this same session equals value, it’ll be like, oh, that must be Maria And so I’ll show Maria her messages and her profile page and all that comes with it in a website So let’s take a step back because the mechanism is as relatively simple as this, even though it’s certainly arcane and you have to kind of know what you’re doing to leverage this But is there a threat here? Like, why might cookies be dangerous and why do people tell us occasionally to delete them? AUDIENCE: If some one gains access to [INAUDIBLE] DAVID MALAN: Yeah, if someone gains access to your cookies, you could masquerade as someone else like Maria And so in the sort of equivalent at a club or a part would be if you kind of do this with someone else’s hand and try to say, hey, we’re both here even though one is now backwards, that would be kind of a duplication of the session value So you probably want to make sure these are encrypted And what’s the easiest way to ensure that the traffic between you and a server is encrypted? AUDIENCE: [INAUDIBLE] DAVID MALAN: Not hashing necessarily AUDIENCE: SSL DAVID MALAN: SSL or the HTTPS prefixed URLs And thankfully– and the story has changed over the years Thankfully, web servers are getting better at supporting HTTPS because then all of this stuff, even though the browser can see it and the server can see it, it’s at least encrypted or scrambled to anyone who’s eavesdropping and trying to see what’s going on between the two But there have been bugs along these lines In fact, it was just a few years ago in CS50 where just days or hours prior, a security researcher had presented at a conference some very easy to use software via which you could hijack other people’s sessions, or session highjacking being the term of art here And what he had implemented was this plug-in for Firefox, another browser And that would allow you, when you ran this guy’s plug-in, to just sniff all of the wireless traffic in the same room And it would show you, so user friendly, a list of all of the session cookies that were kind of going back and forth through the air, so to speak, that were not encrypted And this was, I don’t know, four or five years ago When HTTPS existed, it’s just not, a lot of websites were actually really using it So a lot of really popular websites weren’t using it And so the demonstration in class, which thankfully we can’t do anymore because most websites have been more secured, it was as easy as clicking a button for me to log into that here Rose’s Facebook account because she was using Facebook during lecture and unbeknownst to her, so now was I And it was simply a matter of capturing this kind of value Moreover, if the session cookies are stored, as they’re called, on your computer’s hard drive, there’s just more remnants of where you have been on the web Not just that little drop down menu, but elsewhere on your file system might be reminders for better or for worse of where you have actually been So for these and other reasons do you sometimes want to clear these or at least be mindful of them And frankly, from a development perspective, one of the reasons why we keep encouraging you students to use incognito mode occasionally for development purposes is to essentially clear all of your cookies Incognito mode gets rid of all of your cookies temporarily so that you can interact with the server in a more predictable way And for software developers, that’s just useful not to be confused by older stale cookies Any questions then about cookies? All right, so who cares? Like, that’s all fine and good Now we can be a little more paranoid when using the web But what can we actually do with this now that’s actionable? Well, if I go ahead into CS50 IDE, let me go ahead and open up an example that you can also look at later on via the course’s web site And this is a little web-based store that I whipped up It’s super common these days to, like, buy things on the web And of course when you’re buying things on the web, hopefully

the website is remembering what you are interested in If you go to amazon.com and click Add to Cart, where does that go? Well, let’s think about it Amazon is somehow remembering, after I logged in, who I am And we can assume that they’re doing that via cookies already So they’re putting some handstamp on my hand and my browser is reminding Amazon on every page click that I am me who logged in previously So what do you think a server is doing any time you add something to your cart or quantity two of your car or something else in your cart? Where Where’s that information being stored as well? Say again? AUDIENCE: [INAUDIBLE] DAVID MALAN: Also– so in the header, it suffices just to put that cookie because you proposed actually earlier that we can store stuff in a server, whether it’s a CSV file or in RAM or today in a database If you can store anything you want on the server, it suffices just to remember via this cookie that the person has logged in before and uniquely identify him or her thereafter by that cookie’s value– 123456 or whatever it is Yeah AUDIENCE: But there’s a list associated with your cookie? DAVID MALAN: There’s a list associated with my cookie That is actually the perfect segue to, yes, exactly And let’s take a look at what that actually means So this is a web based application To those of you in the audience who weren’t here last week or the week prior or the whole semester, this is a programming language called Python It’s the language we’ve now started using over the past week And this is using what’s called a framework, freely available software that other people wrote, called Flask that makes it easier to implement web based applications or websites And what we’re doing in CS50, now recall, is not making static websites We’re not talking about web design, per se, which tends to be the art of creating static content, very beautiful informational websites, but web programming, whereby we’re actually using logic to generate web applications or web sites dynamically And so Python lets us do this So this, recall from last week, is the magical line that says give me a Flash based web application And with this comes certain features, like the ability to say, hey Flask, define a route for slash That means anytime the user visits slash, the default page on a web site, return what file, just to be clear? Index.html And then we looked last week at where those index.html are and that they are templates and so forth But this simply means render index.html So recall that I can run this web app as follows If I go into the store directory and type flaskrun, I have now turned on a web server, so to speak, in the cloud, and it’s literally telling me what URL to visit So I’m going to go ahead and click on this and click Open And we see a super simple store Let me zoom in It’s really ugly– haven’t used any CSS This is indeed not web design This is all functional today And you’ll see, even though the projector is a little cloudy, that there’s three text fields there There’s a number for Foo, a number for Bar, and a number for Bas, which if I’m familiar are just go-to words in computer science when you need to talk about something that doesn’t need a specific name It’s like x, y, and z in math So right now, here’s a store a la Amazon.com that sells three things– Foo, Bar, and Baz And I can buy any number of these things But what we haven’t had yet is the ability really to allow a user to interact with a web form and then store that information and change that information We kind of scratched the surface last time with the CSV file for [INAUDIBLE] But here now we have the ability to actually store something in a database So let me go back to the IDE for a moment and focus on exactly that So first of all, here is how this program works I’m going to go ahead and buy myself one Foo, two Bars and three Bazes, whatever those are I’m going to go ahead and click Purchase And now I see my shopping cart And just to be clear, if I hover over the URL, I am now at slash cart preceded by the long URL, which is where I began So notice what’s neat here I can reload and it’s still the same I can reload, still the same I can even close the tab, open a new one, go back to it, and it’s still me Now let’s see what’s actually going on underneath the hood in terms of code Well, to C/cart is actually super simple Just render cart.html But cart.html, which I wrote before this morning, didn’t know in advance presumably that I was going to buy one Foo, two Bars, and three Bazes So it’s probably not hardcoded So there’s this other feature of templates in a web application like this one whereby in addition to telling the computer show me cart.html, recall that you can pass in values And I think we did this once last time So cart is just the name I’ve given to this concept, like a shopping cart And I’m going to pass it something called a session And a session is the term of art that describes whatever it is the server is remembering on that user’s behalf per his or her cookie So the user’s browser has the hand stamp, the cookie– 123456 The server actually stores the session, which is just going to be a box filled with values, something

that has been put into that box before And so what I have now in my cart.html is this Let me go into templates, open up cart.html And this is going to look especially cryptic But for those who’ve seen it before, recall that this just means descend from or inherit from layout.html That’s where, where, like, of the standard HTML is– head and body and all that Then this says, hey, server Here comes the body of my website And there’s relatively little in there Here’s my big bold title– cart And that’s why a moment ago, we saw exactly that, big bold cart And then over here, notice that I’m using Jinja, that templating language– very lightweight, very Python-like, and you’ll get more used to it over time Notice that we have here a for loop saying for each item in cart, go ahead and do this colon that, which looks super cryptic But does anyone recall perhaps from [INAUDIBLE] last time, like, what does this syntax mean with the two curly braces on left and right? AUDIENCE: Variables DAVID MALAN: Variables– just plug in these values here And now as an aside, it turns out that these curly braces are actually escaping our data, so to speak, in a way so that if the user types in kind of dangerous characters at his or her keyboard, this prevents that from becoming a problem, but more on that some other time And so this is just going to say item colon cart bracket item And this might be new syntax to some Where have we seen square brackets before? What does that mean in C? AUDIENCE: Arrays? DAVID MALAN: Arrays So in Python, we still have arrays, but they’re called lists But we have another feature too, and you wouldn’t know this just by glancing at the syntax But when you see square brackets next to a word like cart inside of which is not a number, but it’s going to turn out a word This word is literally going to be Foo or Bar or Baz This is a way of using a hash table or a dictionary or a dict in Python, as it’s called, whereby if the cart is what’s been passed into this template and inside of the cart are a bunch of items like Foo and Bar and Baz, you can get the number of Foos by doing cart brackets Foo, which would look, if I hardcoded it, like this You can get the number of Bars by doing this And you can get the number of Bazes by doing that And yet if I just plug in item, which is a variable, I’m going to get out, within this loop, the Foos, then the Bars, then the Bazes, and so forth So what is the end result? Let’s go back to the browser When I hit reload here, the reason we’re seeing Foo colon 1, Bar colon 2, Baz colon three, is because of that template And if I reload again and again and again, I’m seeing the same thing because my browser is probably doing something So let me do that Let we go to inspect Let me go to Network And just for good measure, and you should get into this habit too, click preserve log Because this way, if you change pages, the log doesn’t go away You can still poke around what you’re seeing And again, this is my Network tab So I want to look at what Chrome is doing to talk to this web app I’m going to go ahead now and I’m going to hold Shift and then click reload Or if you click and hold– you’ve probably never noticed this But if you click and hold on your own reload button in Chrome, you can do a normal reload, which is what all of us do all the time, or a hard reload or an empty cache and hard reload, which is just a way of saying get rid of all of my cookies, all remnants, and just forcibly reload everything, which is great for software development so we know what state we’re always in But you can also do that by holding Shift– whoops– and clicking reload So let’s do that Reload– and here we go That made one request because there’s no images or CSS or fancy stuff to send more requests This is going to be a little overwhelming at first glance The IED itself uses cookies for other purposes So you are seeing bunches of cookies that have nothing to do with this application– has to do with the programming environment But what we can focus on is this here Notice that any time I get a response from the server, it’s indeed sending me set cookie and then some value Now this is a longer value than 123456, but it’s the same thing– a big random number So what’s happening is that is the server’s way of telling my browser you are user 123456 or whatever that big long number was And my browser is now supposed to present that again and again and again So lastly, what is actually going on when we continue shopping and let’s say buy 10 of these and 10 of these and 10 of these and click Purchase? Notice that if I look at the form, this is going to slash update And it’s via Post And so if I now go back to the code to see what happens– let me go back into application.py, focus on update This now is how I’m updating the contents of my server side cart I am saying for each item in the form that was submitted, the requests form, go ahead and update session bracket item, which is going to be Foo or Bar or Baz because that is what

was in the form, and store there this– request.form.get So get me the value of that item, whether it’s 10 and 10 and 10 or 1 and 2 and 3, whatever the numbers are that the human typed in And why do you think we put this word int? Why might that be necessary? Yeah AUDIENCE: [INAUDIBLE] DAVID MALAN: Yeah, it’s simple as that It’s kind of a casting in C What the user typed in was text It might look like numbers, like 1, 0 Certainly looks like 10 But it’s not a number That’s a string that looks like a number So using int converts it to an actual number so we can do math on it So long story short, what is it that made this possible? We’ve not used this before, but you will have seen this perhaps in P set six or rather P set seven’s distribution code, which is now on the web site There’s a few lines of code that just enable sessions With those few lines, are we able to tell Flask, hey Flask, start sending cookies and expecting cookies back so that the programmer can use what is called a session, which I’ve additionally imported up here? So I’ve just added some additional functionality And if you’ve ever wondered where all this data is going, let me go ahead and stop the server, clear my terminal and type LS A moment ago, the only two things in this folder were application.pi and templates Now there’s apparently a directory called Flask session So if you’ve ever wondered where Amazon stores the contents of your shopping cart or where Facebook stores Maria or Brian once they’ve logged in, it’s probably in a folder in a file like this where these big long strings represent your cookie value or, better yet, it’s in a database And so that’s why those are there– because I apparently created four cookies by reloading and experimenting a few different times OK, any questions on what the potential then or the usage of cookies and sessions are? All right, so let’s then transition to solving problems a little more powerfully with these things called databases So here is, like, a depiction of what we’ve been doing the past week and we’ll do a bit more today and you’ll do with problem set seven as well We have been having browsers and servers talk to one another using kind of a certain paradigm And the paradigm is generally called MVC, Model View Controller, though we didn’t call it that last week And MVC means this– you are writing code that is called controller This is like the logic, the intellectual property, the business logic of your application Like, if a user submits form, then add update contents of shopping cart Any of those kinds of ifs and elses and loops are in what’s called your controller code, AKA application.pi You might also have views, V in MVC, which specifically are your templates, the idea being that you separate all of the interesting, juicy, intellectual part of your program in the controller code, the application.pi, and all of the important but kind of aesthetic stuff in your templates directory, AKA views This way, among other things, you, for instance, can implement all of the logic of a web site whereas a friend of yours who is much better at design certainly than me can do all of the aesthetic stuff, all of the views, in the templates directory, and you can collaborate a lot more effectively But there’s one more letter in MVC It’s Model View Controller And what we’ll focus now on today is the M in MVC Model is just a generic way of talking about data– like where does the actual data go So the mental model to have moving forward as you write code, even though the lines are not going to be always as clear as this picture suggests, is any time you’re writing code in application.pi that’s probably controller code Like, that’s the intellectual workflow of your application Anytime you’re doing HTML or CSS or Jinja, that’s the view of your application, the aesthetics, the presentation of information And any time you’re reading and writing data, long term or short term That’s the model And sometimes the lines will be very blurry and it could all be in the same file But those three types of operations are the mental model to begin to adopt So let’s get there You’ll recall that in week zero, we introduced this guy, Scratch, and the graphical programming language with which you began the semester A week later, we introduced C, which had the same ideas and capabilities for the most part but using a keyboard only instead of graphics and a GUI Then we transitioned a week ago to Python, a couple of weeks ago now to Python, which is very similar in spirit to see But as you may recall with great delight or frustration, you can get so much more work done quickly– for instance, P set five– using the right language And so here too we begin to use a new tool for the job called SQL, or Structured Query Language, or S-Q-L. This is yet another language, but it’s much smaller than Python and C as we shall see today But it is a language that you typically use when you want to talk to a database So based on your familiarity already, what is a database even if you’ve never actually used or programmed with one before? AUDIENCE: [INAUDIBLE] DAVID MALAN: Place where your store data So a database is a place where you store data- a base where you store data even perhaps And so that’s true And what does that actually mean?

Well, a database, like a web server, is actually a piece of software We might think of it is this, like, physical entity, but a database is technically a database program that is listening for connections just like a web server or an email server And any time it gets requests, it serves up a response So we have the same client server relationship that we do with the web itself Now the database hopefully is better and smarter and faster at storing data than certainly little old me And it’s hopefully better than something like CSV files Like, recall we used CSV to store the registrants for [INAUDIBLE] last week I think it was me and Rob and Maria all at once Then what was good about that? What problem did we solve by introducing CSVs with that example? Yeah AUDIENCE: [INAUDIBLE] DAVID MALAN: You didn’t have to go through my inbox, right? One of the other versions of the registration program were to just email the proctor in charge of [INAUDIBLE],, which is fine because he or she can just use labels and keep track of it there But that’s not a database You’re Just kind of abusing Gmail as a database So why not put in a Text file instead like a CSV? Because what kinds of programs can open CSVs? Yeah, like Excel and Numbers and similar spreadsheet programs You can import it into Google Spreadsheets as well So that was a plus of using CSVs But what’s perhaps a minus even though we didn’t really experience the minus so much? AUDIENCE: [INAUDIBLE] DAVID MALAN: Harder to what? AUDIENCE: [INAUDIBLE] DAVID MALAN: It is harder to do stuff with it because at the end of the day, it’s just a stupid text file, and we humans have agreed to put commas in between values we care about But that’s it Like, there’s no features You open up that file, you’re just going to see ASCII text and commas with them You get no, like, features, no functionality And do you have another thought? AUDIENCE: [INAUDIBLE] DAVID MALAN: It’s inefficient why? AUDIENCE: [INAUDIBLE] DAVID MALAN: It’s harder to find things Certainly not in the case of, like, me and Rob and Maria But as soon as you start having hundreds of students registered or thousands of customers in your database or hundreds of thousands, like, in CSV, the best they can do is linear time for everything You have to search the entire file top to bottom So wouldn’t it be better if we can borrow ideas from week zero, integrate them into the context of this problem, databases, and actually query for data, ask for it, change it, delete it, and edit it much more readily than simple little text files allow? So what should you be thinking about when you think about a database? Well, there’s different types And today, we’re going to focus on what are called relational databases, which is a fancy way of saying tables of columns and rows So this is a screenshot of Google Spreadsheets with which you’re likely familiar The projector seems to be hiding the fact that there are actually rows and columns here And instead, we’re just seeing a big white box But there are indeed rows and columns inside of here as you can see on the screen And if I use a [INAUDIBLE] this is a screenshot So I can’t even enable rows and columns So here, we have our columns and rows So why is this useful? Well, if you’ve ever store data for your student group, or for your customers or for other applications around the house, you’ve probably been in the habit of storing things in rows and columns And what does each column typically represent in a spreadsheet? What kinds of things might it represent? And here’s an actual version of Google Spreadsheets And let me actually highlight this and turn on, just because, darker lines So there’s our rows and columns So when you’re using a spreadsheet, maybe from personal experience, like, what do you put in the columns? I’m sorry? AUDIENCE: [INAUDIBLE] DAVID MALAN: Categories of what? Give me a concrete example AUDIENCE: [INAUDIBLE] the name of the student and lab that they’re in and– DAVID MALAN: Oh, what a delightful example So yes, we can say, like, the student’s name and the student’s dorm and maybe their email or if we collected their phone number and bunches of other things And you know, just to be sort of tidy, we might boldface it, and you can use features like this, which just kind of lock that row there But these are just GUI features These are not sort of intellectually that interesting But what do I typically put in each row thereafter? The first row is kind of special usually, which is why you can kind of lock it off What do you put in the rows? AUDIENCE: [INAUDIBLE] DAVID MALAN: I’m sorry? AUDIENCE: Elements [INAUDIBLE] DAVID MALAN: Yeah, the actual data– so, students’ names So if, like, David registered from Matthews, you would put his information in there And then, like, if Rob registered from Thayer put his in– oh, that’s not– Rob’s from Thayer, not Rob from Bowden And then Maria, who– damn it, I asked her this the other day Where does she live? [INAUDIBLE] Matthews, just like me, which is why I should remember it OK, so here’s– we now might have multiple rows in this database, each of which represents an entity would be the fancy way of describing where an entity is like a real world thing or a physical object or whatever it is you care about So this is how we might form at this And of course literally, we might format things using Excel or Numbers or Google Spreadsheet’s, like, format column, which kind of presents the data in a different way Maybe it centers it to the left or– the lines to the left or right Maybe it removes leading zeros Maybe it adds dollar signs, percentages You can kind of present the information in different ways But for the most part, at the moment, it’s just text that I’ve typed in But if I want to actually find someone in this spreadsheet, what can I do?

Well, I can do Command-F and then type in Rob And there we go We found Rob But what did the spreadsheet do? Pretty much looked from top to bottom looking for R-O-B and as, soon as it found it, highlighted that row And if I had thousands of rows or tens of thousands of rows in the spreadsheet, Rob might be all the way at the bottom at which point it could take longer to actually find all instances of Rob And in fact, programs like Excel and Numbers and even Google Spreadsheet eventually just start to break down In fact, I experienced this in grad school myself I was doing some research where I wanted people to analyze data and it was just convenient to be able to double click on my CSV files, open them in Excel, and look at or search for some of the rows that I cared about But it turns out that because of the long running simulations I was doing, I had, like, over 65,000 rows of data, and Excel just wouldn’t do it I would double click and, sorry, this file is too big because someone at Microsoft, in prior versions of Excel, had decided, first and foremost, surely no one needs more than 65,000 rows in this product But specifically, they used a number of bits to represent the row number If you recall, we tend to deal in powers of 2 or multiples of 8 specifically– so 8 and 16 and 32 and so forth Well, 16 bits, 2 to the 16th, is 65,536 And indeed, I must have had 65,537 rows or more So Excel just kind of wouldn’t cooperate, and I would have to use an actual database like Microsoft Access or Oracle or, as we’ll see today, something like SQLite So as soon as you kind of hit some ceiling with the tool whereby it’s slow to search or the program just can’t open all of the data or you can’t kind of edit information or delete information any better than linearly, it’s probably time for an actual database And one such database is called, indeed, SQLite So SQL, Structured Query Language, is the language we’re about to see SQLite is like a very free and very lightweight implementation of this database And it’s a lightweight in the sense that you don’t need special software licenses You don’t really need to install it All you have to do is store data in binary format So up until now in CS50, we’ve pretty much stored almost everything in ASCI or text format except for P set 4’s images Well, today we’re back to storing things in binary format, zeros and ones But again, we are well past the point of caring what those zeros and ones look like, the patterns there of So SQLite is going to handle all of that complexity for us, and it’s going to store in one big binary file containing zeros and ones the same kind of information as David and Rob and Maria and others but much more efficiently because one of the things you get from a database is the result of a lot of smart people’s effort trying to figure out how can we, on behalf of the world, store data more efficiently? How can we provide users around the world with searching and deletion and editing capabilities more efficiently than he or she could implement themselves? So again, in the spirit of CS50, we sort of are standing on the shoulders of people who’ve come before us Now what is this going to mean in real terms? We’re going to use at least one of two tools There’s a tool called PHP lighted min, happens to be written in another language called PHP But that’s incidental We’re going to use it as a graphical tool with which we can interact with the database program But there turns out there is also a terminal window version of a management tool called SQLite or technically SQLite 3, which is just a text based way of interacting with the database as well But that’s just how we’re going to see the database visually in my terminal window or in my browser At the end of the day, we’re going to talk to our database using actual Python code But before we get there, let’s seed ourselves with some of the basic building blocks that we’ll have at our disposal with this language So unlike C and Python, where we’re generally focused on the logical controls, conditions and loops and functions and so forth– some of those do exist in SQL But we’re actually going to focus really on just the operations, so to speak– the verbs that SQL supports So it’s a little different mental model to have than C and Python Specifically, perhaps the most common operations we’re going to use when storing data in not a spreadsheet like Google but rather a binary file like a SQLite file is these five operations We can create tables We can create– we can insert rows We can select rows from the table We can update rows in the table and we can delete rows from the table So these are kind of like what you would expect you would get from a database program It’s just these happen to be the terms of art as well Like, these are the verbs we use And it’s conventional to capitalize them, but they don’t strictly need to be So let’s look at a few examples now If I wanted to, in an actual database program, create kind of the equivalent of this idea– rows and columns and things called tables–

here’s how I might do that So in my administrative program, which we’ll see in a moment, or in code, I might do lines like this, one at a time We’ll review So in yellow now is CREATE TABLE So if you want to create the binary equivalent, the computer equivalent of like a Google spreadsheet for [INAUDIBLE] registrants, you would literally do CREATE TABLE quote unquote registrants where that’s the name of the table or the sheet you want to deal with And then this didn’t fit on one line So I wrapped it But this is technically one long command Then in parentheses, you specify a, separated list of all of the columns that you want to have in that table, but– and this is fancier than Excel and Numbers and Google Spreadsheets– you specify a few other details as well For instance, if my leftmost or first column is going to be called ID and my second is going to be called Name and my third is going to be called Dorm– so similar to what you proposed earlier but we’re not going to worry about email and phone number and the like And I have added, for good measure, a unique identifier, an integer to identify each freshmen registering for sports There’s a few other details I’m clearly providing Like, what type of information is a name going to be clearly? AUDIENCE: [INAUDIBLE] DAVID MALAN: Text, and indeed, SQLite has a data type called not string but literally text Similarly, as dorm going to be text But ID is going to be a little different It could be text But we get better efficiency if we just use numbers to uniquely identify humans, right? Because even in my name, D-A-V-I-D, how many bytes is that minimally? How many bytes? D-A-V-I-D. Five– yes, five bytes total for D-A-V-I-D But if we instead represent me in a computer using a number like the number one or two or even the number 2 billion if we have a lot of users, how many bytes does it take to store that number? AUDIENCE: [INAUDIBLE] DAVID MALAN: Well, even fewer If I just rep– if my Harvard ID number is just a single number, like one or two or 2 billion, how many bits do you need to represent an int like that? Typically, we’ve used four You could use eight if you want a bigger integer, but generally speaking, it’s going to be fewer than the number of characters you’re actually carrying about And so that’s why I might use an integer We’ll come back to what primary key means in just a moment But long story short, that line, those lines in yellow if executed in a database program are going to create the equivalent of a spreadsheet containing three columns that I care about Thereafter, if I want to insert someone like myself into that table This is how you use the SQL language to do so You literally execute insert into registrant, which is the name of the table, then a, separated list in parentheses of the columns for which you have values that you’re about to provide And this allows you to change the order if you really want, but I’m just keeping it clean and going left to right Then you literally write values And then in parentheses again, you list a comma separated list of the values that you want to plug into those columns And again, it’s up to you to make sure that if you say ID first, you put a number first Name second, you put the name second, and so forth That’s where we’re in control Meanwhile, if we want to search for everyone who has registered for [INAUDIBLE],, we’re going to see syntax like this You’re going to say to the database select star everyone from the database, specifically from the table called registrants And that’s going to return to you literally every row somehow in code from the table Meanwhile if I want to change my name because I wasn’t sort of following directions and they technically wanted my full name for sports, well, here I can do update registrants, the table, set the name column equal to David Mailand, but this last part is pretty key If I don’t put where ID equals 1, what intuitively do you think might happen in the database? AUDIENCE: [INAUDIBLE] DAVID MALAN: All the names go to David Mailand, which is probably incorrect And some of the C course’s head teaching fellows will remember years ago– we actually had a teaching fellow named Michael [? Tingly ?] whose nickname was Tingles And we accidentally executed a SQL query omitting this condition, this predicate Where we should have said where ID equals Michael [? Tingly’s ?] ID number, instead we left this off And thereafter, we had like 700 Michael [? Tingles ?] registered in the class until we restored from backup So it can happen to you as it did to us Finally, if you wanted to delete someone, same principles, just different verbs– delete from the table that you want to delete from where you are specifying an actual ID in this case And actually just for good measure, though it doesn’t strictly matter, let me be consistent and just capitalize that Where as well The reason for the capitalization, just to be clear, is that it kind of helps the human eye to distinguish what is, like, SQL syntax from words that we humans have invented for own spreadsheet And notice a few characteristics as well I have standardized on all lowercase for my column names ID, name, and dorm, which is common, but not strictly required The table as well is all lowercase But what is absent from all of these field names is I’m not using spaces

I’m not using funky characters like exclamation points, like “registrants!”, or anything like that I’m just kind of keeping it clean with very simple characters and that’s good for portability It’s very commonly the case And you might have seen or experienced this in the IDE Any time you start using spaces or funky characters, computers sometimes get confused because they’re not expecting them So the best practice with things like database design is just avoid those issues altogether and keep things simple Use underscores instead of spaces if you really want to put something– a gap between two things Now meanwhile, there are different data types There’s not terribly many, and the only ones we have to care about for the coming days are these here, one of which is kind of strangely named But SQLite, the database we’re using henceforth, supports text as we’ve seen, integers as we’ve seen, or AKA int– some of these things have aliases or nicknames Null is going to be kind of the opposite of having any value You can actually specify, no, there’s no value here Real is going to be what, perhaps? AUDIENCE: [INAUDIBLE] DAVID MALAN: Yeah, like a real number, like a floating point value, as we called it in C and in Python And then BLOB is binary large object So if you actually want to store 0’s and 1’s in some form in your database, you store it as a BLOB, which isn’t super common because there are better ways to store binary data like, in files But it is possible as well And it’s worth noting that there’s kind of sort, of some other types, but they’re not sort of officially there SQLite is nice because it’s pretty flexible There are a lot of database programs out there You’ve probably heard of some of them like the ones I rattled off– Microsoft Access, Oracle, MySQL, Postgres SQL, and others SQLite is just an alternative that’s pretty flexible And so if you try to load data from like another database like Oracle into SQLite, it will actually tolerate unofficial types like date times, which look like years and time– or dates and times literally– numeric, which is when you’re not sure if it’s going to be a floating point number or an integer, the database can figure it out for you and some others as well So don’t be confused when reading up on SQLite If you see some things that aren’t wholly consistent, it’s just consistent with that kind of flexibility And in fact, here’s an exhaustive chart of all the things from left that map to the right So in the right are all of SQLite’s official data types, these five here And you might see on the internet in SQL tutorials and like bunches of other types here The most popular might be, like, bigint, which is a 64-bit integer, which is really big, varchar, which is a variable number of charts– either fewer or more, depending on the data– and lots of other things as well So just realize they’re out there, but the five on the right are the ones we really care about SQL also has some built-in functions You can do this in Python code too as we’ll see But date and time and datetime allow you to get, like, the current time So if a user registers for your website and you want to record when he or she registered, you can call functions like this and the database will just figure it out for you You don’t have to check the computer’s own clock But most importantly, there’s going to be some of these decisions But before we forge ahead with too much more, let me pause for any questions Yeah AUDIENCE: [INAUDIBLE] when you [INAUDIBLE] DAVID MALAN: Ah, good question Typically, no But if you want to be really precise, yes So I was really precise and I actually read the documentation this morning to make sure I got all of those quotes right The rule of thumb if you’re curious– though for the most part, this won’t matter and it shouldn’t hang up– is that any time you are referring to the name of an identifier, as it’s called, like a column name or a table name, you use double quotes with SQLite Anytime you’re referring to a literal string that the human has typed in somewhere like with David Mailand and like with Matthews, you use single quotes So if it’s kind of user input, single If it’s a name in your database, then it’s double coated, but it doesn’t typically matter So don’t fret two much So let’s consider then for just a moment where we started this particular conversation We were trying to create a database or spreadsheet for [INAUDIBLE] IM’s, but we kind of stopped short of the juicy part Like, I typed in some names and some dorms But I could have started typing email, which would have hopefully some standard format– something at something dot whatever And then phone number is kind of interesting, right? Like, how should I store a number? Like, if I just want people calling Harvard’s main number, is that, like, 459– what is it 5,000? Well should I store it like this– 10 digits and two hyphens? Should I instead store it without the hyphens just to kind of save space? Should I, need to be consistent with international folks, store the country code as well, which now Google is confusing for math? So that’s what Harvard’s phone number is when you add the numbers together apparently How do you store the information? Or better still, what should you expect the users to type in? When you visit a website and you are asked for your phone number to buy something or create an account, like, in what format

are you typically prompted for your number at least in the US? AUDIENCE: Area code, [INAUDIBLE] DAVID MALAN: Yeah, you’re often prompted [INAUDIBLE] like an example like this And then you have to type it in and hit Enter And then it’s stored, like that What else have you seen– probably the other formats too? So which way should you do it? AUDIENCE: [INAUDIBLE] DAVID MALAN: Yes, yes, all of these ways are perfectly reasonable, and any website that forces you to type your data into one format is stupid Like, it is poorly designed And this is one of my greatest– well, it’s not my greatest pet peeves But like, one of my pet peeves is when a web site just completely technologically unnecessarily puts the burden of the human to format the data Like, I should certainly be able to type my information in like this or with the plus one or without the parentheses or with an additional hyphen or the like because you know what you the programmer who made the website can do? You can just use some kind of pattern matching in the spirit even of P set six, throw away all of the punctuation, and then store just the digits if you want or go in and forcibly add the hyphens where you want them or the parentheses where you want them The burden does not need to be on the human And there’s actually compelling UX, or User Experience, reasons for this beyond me going off kind of on a rant Like, if you can’t copy and paste something into a field, that is a bad user experience And if you’ve ever tried to just copy one field into another and the website is disabling that for you, it’s not the best practice Worse yet are websites that don’t let you paste your passwords into the forms because if you store your passwords, as is good practice, in like a password manager, encrypted somehow, it’s better to have a really long password and then paste it into the form than memorize a really short password that someone else could guess So in short, as we move forward with web programming, don’t necessarily take as canon, like, what you see out there, but consider for your own users, like, what would be the best way to do this and how can I, knowing programming, accommodate different styles and expectations as well? But at the end of the day, there’s an issue here because is this phone number now a number, or is it a string or text? AUDIENCE: [INAUDIBLE] DAVID MALAN: So OK, so I heard a few strings or text So that’s good because SQLite supports text So I could specify, when I convert the spreadsheet into SQL commands and a SQL database, I could use text And so that’s probably a good thing What about zip codes, right? Zip codes, at least if we do the first five digits in the US– like 02138 to Is that a number or is that text? AUDIENCE: Text DAVID MALAN: Text? Why? AUDIENCE: [INAUDIBLE] DAVID MALAN: I’m sorry? Say again? AUDIENCE: [INAUDIBLE] DAVID MALAN: Yeah, it’s more of a category than it is a quantity, certainly Like I don’t have zero, one, two, three, eight of things That is actually just meant to be a unique symbol And in fact, as a side effect of the spreadsheet trying to be helpful, here is an actual bug, or reason to store it as text I typed in 02138, which is Cambridge, Massachusetts, one of its largest zip codes And unfortunately, Google just decided, you don’t really need that leading zero, do you, and it got rid of it And so this is because, at least in Google Spreadsheets, my data here, if I go under Format, it’s being stored automatically Google, for better or for worse– often for worse– is inferring that, if it looks like a number, it is a number, therefore let’s throw away the leading zeros, which humans don’t need But if I change it to plain text and now do 02138, now it kind of sticks And another sort of true story, years ago, I used to use Microsoft Outlook for my email And then eventually, I switched to Gmail And before I did that, I wanted to copy my contacts, like my address book from Outlook into Gmail And so Outlook actually has a feature where you can export a big CSV, literally, from Outlook And that contains columns of, like, all your friends’ names and numbers and addresses and zip codes And I foolishly didn’t realize that I probably shouldn’t open the file first in Excel to look at it, probably instinctively hit Command S or Control S just to save it, because that’s like a habit As soon as I did that, Excel presumptuously did a Google just did, and dropped all those leading zeros And to this day, like 10 years later, I’m still finding friends whose addresses are Cambridge, Massachusetts 2138 for that very reason So data formats matter And we actually have a number of decisions to make, and moreover, if we want this database to be highly performing– that is, quick– we actually have a few other design decisions And so let’s take our five-minute break here, and when we come back, we’ll actually wrestle with how can you store the data most efficiently so that your web applications are super fast So we are back And let’s see what problems now arise besides the most immediate, which was the phone number So let’s consider that these aren’t necessarily students registering for [INAUDIBLE],, and so therefore they don’t just have dorms and so forth, but maybe these are actually like customers in a database, Amazon customers or the like And so people are indeed going to have names, we’ll say And people are going to have addresses, address

And people will have phone numbers still, email, and maybe some other identifiers, as well So when it comes to storing actual data here– and just for clarity, these will be my column headings now, so special first column– so what’s my name here going to be, David Malan? Should it be David? Like, how do you actually think about something as trivial as this, and what are some of the upsides and downsides? Like, which should it be? AUDIENCE: David Malan DAVID MALAN: David Malan, OK So David Malan is what we store OK? But now, later on, I realize I’d really like to send customized emails to my customers, and it feels kind of lame and informal if I say dear David Malan, where clearly it’s not very intimate I’d like it to say, dear David comma So how do you address that? AUDIENCE: [INAUDIBLE] DAVID MALAN: OK, so maybe we should store first and last names separated So to do that, maybe I’ll just kind of redo this, and actually call this First, and then call this Last And so now, I can keep my two columns separated Other thoughts? That’s pretty good Or– AUDIENCE: [INAUDIBLE] DAVID MALAN: Sorry? AUDIENCE: [INAUDIBLE] DAVID MALAN: Oh, so we have like the greeting, as well, which you might be asked by a dropdown or a text box, too So maybe we need that, as well So technically, I should probably move this over like this And this will be like the greeting, and maybe this will be Mr. or some number of other symbols So we might have to deal with that, as well What about address? Let’s move on to that, because honestly, with most of these questions, there’s not necessarily a best answer There might be some best practices, but even then you’re going to hit corner cases For instance now, anytime I do want the user’s first name and last name, I’m gonna have to concatenate them together, which is fine It’s not a big deal in Python It was a bigger deal in C So there’s these tradeoffs Like, I just have to now do that, or write more code later What about the address? So it might be 33 Oxford Street, the CS building in Cambridge, Massachusetts, 02138 USA Let me even make more room for all of this Good? Bad? Why? Let’s go here AUDIENCE: [INAUDIBLE] DAVID MALAN: Sorry? AUDIENCE: [INAUDIBLE] separate the data [INAUDIBLE] zip code [INAUDIBLE] DAVID MALAN: Well you say need to separate the data into street and city and country and zip code and state, but why? AUDIENCE: [INAUDIBLE] right now to sort people by [INAUDIBLE] DAVID MALAN: Yeah, that’s a great example So if everything has just kind of been this long string, which we humans and the postmaster general in the US can understand, and computers can parse, it’s not that easy to just glean what we care about from that whole long string Like, we’re gonna have to use some kind of heuristics, we’re gonna have to look for all numbers to get the zip codes And I can, as you say, not sort everyone who’s in Massachusetts, or everyone who’s in 02138, because like it’s not a separate field And so even mechanically in Google Spreadsheets, I can’t sort by anything other than 33 Oxford Street And I probably don’t really care to sort people by their street addresses, which really have no notion of ordering that’s interesting So we probably want to split this up So maybe this is actually Street, and maybe this is actually– let me shrink this to make a little more room now– maybe this is City, maybe this is State, maybe this is Zip, maybe this is now Country And then over here, we have Email and Phone and so forth And so now, I could do 33 Oxford Street And then I could do Cambridge, Mass, 02138, USA, malan@harvard.edu, 6174495 See, this is why you shouldn’t allow user input in different formats 495, 5000 and so forth OK, so suppose we do that But you know, Rob has an office in the CS building, as well And so he’s actually Mr. Rob Bowden So now he and I are both there And oops, we didn’t fix this, so technically let’s go back here 02138 And now it’s like rob@cs.harvard.edu And maybe we share the same office number And there are certainly, like, dozens of other people in the CS building, if not a couple of hundred So this is actually yielding another problem At least if I keep typing, I’m going to dislike this for some other reason What’s also bad about this? Yeah AUDIENCE: [INAUDIBLE] the office room DAVID MALAN: The office room, yeah So we didn’t specify the room in the building So we’re missing data all together What else really rears its head over time? Yeah AUDIENCE: It’s very redundant DAVID MALAN: What is redundant? AUDIENCE: [INAUDIBLE] wasted space [INAUDIBLE] DAVID MALAN: Which information is redundant, then? AUDIENCE: The street [INAUDIBLE] DAVID MALAN: Yeah, there’s a lot of– I mean, literally I copied and pasted, which in coding in general should kind of be a bad sign or a bad habit, certainly if you do it more than just once And so Cambridge is identical, Massachusetts is identical, zip code is identical And even though this is gonna be a small white lie, it’s probably the case that every zip code actually maps to a unique city

So I don’t– if I know 02138, do I really need to store Cambridge, Mass, Cambridge, Mass, Cambridge, Mass for everyone? No, it’s probably all the same So I should be able to delete some of this data And this kind of messiness is a problem that databases can now solve You could do this in Excel and in Google Spreadsheets and so forth You can quote unquote normalize your data by factoring out commonalities But to do so, the most common way, then, is to start assigning unique identifiers to things So for instance, if I want to go in here– let me give myself a new column, call it ID And I’m just going to be user one, Rob will be user two, and then three and four and so forth We’re just gonna use an int to keep it simple and efficient, because now it’s only like 32 bits, or maybe 64 bits, no matter how long the humans’ names and street names and so forth are Let me just fix the border so that everything looks uniform So now I’ve done this And you know what, if you’ve never noticed, like Excel and Numbers and Google support multiple sheets in a spreadsheet So just for good measure, let me actually rename this to Customers And you know what? I’m gonna go ahead in Google Spreadsheets, give myself another one called Cities And then here, I’m gonna do something like this I’m gonna store over here maybe a let’s say city and state And actually, you know what, because cities I’m gonna claim for the moment are uniquely identified by their zip codes, let’s just put zip code there And then in this spreadsheet, let’s do– preemptively this time– plain text– 02138 shall be Cambridge, Massachusetts And now, back in my customers database, which of these columns, A through I or so, do I actually not need anymore? AUDIENCE: [INAUDIBLE] DAVID MALAN: Yeah, I don’t need this I don’t need this I probably still need zip So let me just go ahead and delete these and start to assuage your concern, which was this redundancy of data Now, I can do a little better, but now at least I’m storing zip code only And actually, this is getting a little lazy Let me get rid of country, as well, and just put this in here, as well, for generalizing zip code to be postal code now, say And this shall be USA So now my Customers table has a lot less redundancy So clear upside is that it just takes up less space, and less space means less money, and just more room for other things So that’s surely good But it probably means too it’s easier to update the data, right? If someone– if Cambridge for whatever reason changes its name back to Newtown, as it apparently once was, we just change it in one place, not for every resident in the Customers table, and so forth So this is just a good principle, right? Anytime in coding we’ve done something again and again, or in CSS even, like we’ve tried to factor it out In HTML, we factored it out with templates or with functions or with other techniques So this normalization process feels like a good thing But you know what? 02138, we can do a little better, because that’s a string We could just store this as a number So you know what I’m actually gonna do? I’m gonna also have in this spreadsheet a column called ID whose value for this city is gonna be one, and then for subsequent cities it’s just gonna an arbitrary number that’s just increasing and unique And so now what I can actually do here is I don’t strictly need to store even 02138 dash whatever whatever, I can just store the ID And I’m gonna go ahead and call that zip ID, just to make super clear to me that now this is just a number And so by starting to assign arbitrary but consistent unique integers to all of our data, you can kind of relate them back to one another by looking up one value in the other So for instance, if you’re looking at this table as a human and you see that, oh, David and Rob live in zip ID 1, you do have to ask a follow-up question, which is, well, what is zip ID 1? You can only answer that by looking at the Cities table, checking it, and being like, oh, Rob and David both live in Cambridge, Massachusetts, 02138, USA So it’s added a step, but it’s also massively shrunk the amount of data we need, assuming there’s more people in the world than just the two of us, and there’s millions of users or thousands of users, or just otherwise context where you have lots and lots of data And in fact, as an example of this, what we put together on the course’s website is a Google spreadsheet that has a whole bunch of sheets This is based on a freely available open source database that looks like real data, but it’s mostly taken from one guy’s iTunes account and then made up names and company names and so forth And you’ll see that this is actually a database for like a digital music store, a la iTunes or Google Play or Spotify or whatever And so there’s different entities in the world of digital music You have album names, you have artist names, you have customers who might buy those albums and listen to those artists You have employees at the company who is selling the music You have genres for those songs Because this is actually a very slippery slope whereby, over time, you’re gonna have lots and lots of data that ideally would be in multiple places at once, but that’s just bad design Keeping it factored out is ideal

So if we glance at this album title, or rather album tab, here are all of the albums that this digital music store sells Here are the unique IDs, album ID, that have been assigned to those albums, arbitrarily but for permanently And then here are the artists who published those albums So the only way to know the artist’s name is– at least in Google, I’m gonna actually have to scroll over to my Artists tab and, OK, here are the artists and their numbers Meanwhile, all of these albums later– or songs are associated with genres So here’s all the genres and the IDs associated therewith, and so forth So you’re welcome to poke around this kind of data later Here’s even data involving birth dates, so dates and times and so forth Here are invoices, like the addresses and the cities of people who bought this music, supposedly, in the sample database But honestly, this is very, very, very quickly becoming really really annoying, right? Because if you the human just so much want to know, OK, who produced Restless and Wild, artist two, who the heck was artist two? This is not good for humans But this is good for computers We just need a language with which to reconstruct what we want and query for and change what we want And that is the kind of thing that SQL is going to do for us So let me change to another tab here in CS50 IDE Let me find the right tab here So if I go back to CS50 IDE, let me introduce a tool called phpLiteAdmin Name doesn’t really matter, but this is a tool that comes with the IDE– and we downloaded it for free off the web and just installed it for you– that allows us to do this I have among today’s distribution code a file called lecture.db db just means it’s a database It could be another file extension, as well, but this is one of the standard ones And notice that– let me close the console– if I double-click this, this is going to open a special tab in the IDE called phpLiteAdmin What I am now seeing is the content of lecture.db Lecture.db is a binary file, zeros and ones, that represents a SQLite database that I made before class So I essentially downloaded all this data, I made a few changes to it, and then saved it in a file called lecture.db phpLiteAdmin, this GUI here in the tab, is just a way of visualizing for us humans what’s in this database And you can see there’s a bunch of tables, I think 11 different tables, that I named identically to the Google spreadsheet So this is the SQLite equivalent of the Google spreadsheet But notice just from the links alone– not that this is a very good design– the overwhelming amount of functionality that I now get I have the ability apparently already to browse the data, search the data, insert, export the data, empty the data, and so forth that we just get from a database And we’ll see how to do that in just a moment If I go ahead and click on Album, I’m gonna see the same data as before within this graphical tool, but again I just have some handy functionality I have the ability to edit each of these albums, delete them But I can also see the artist ID Now, this isn’t all that useful, but let me go ahead and do this Let me go ahead and go to the SQL tab And this is where this tool is actually handy phpLiteAdmin is not something you put on your resume This is just like a tool we will use to help visualize what’s inside of our database, and to try out queries before we actually write them into code I recall from before that one of the operations that SQL supports is Select So let me try that, select star from album And these are the key words from SQL This is the name of my table I could quote it, but it’s not strictly necessary to, so I’m not gonna bother here And if I go ahead now and zoom out– whoops– and click Go, this is going to select for me all of the albums in the album table Now, this is not all that interesting, because just a moment ago I just clicked on the word album and I showed you all of the albums But suppose I don’t care about the album ID I don’t care about the artist ID at the moment Well, let me go to SQL tab again and do select star from album But instead of star, which was the wildcard, what might I want to select instead? Just the name of the album? So let me do that, select– oh sorry, it was called title, title from album Click Go And now I get back a set of rows, a result set, as it’s called, of just the titles Now again, I’m not programming, I’m not using this data But this is the language, the way I can express myself in the SQL language to get back only the data I care about And you know what? Let me go ahead and do this now And this is where it gets more powerful If I go back to selecting all the data, there’s the artist ID How do I get the artist ID? Well let me go to Artist, and there’s that same data, artist ID and all of the names But here’s where SQL gets powerful I can now take one table, one of whose columns is these numbers, take another table, one of whose columns is these numbers, as well, and I can kind of join them cleverly by lining up the numbers that are identical in both The syntax is a little funky at first, but let’s try this Select star from album join artist

I want to join the two But now I have to tell the database what two columns to join It just looks like numbers, but I have to tell the database which columns should line up So artist on album dot artist ID equals artist dot artist ID So this is just a predicate or a condition at the end, but you can kind of read it intuitively Select everything from the result of joining album and artist, provided you join them on album dot artist ID, which is the artist ID column in the album table on the artist ID column in the artist table So that’s kind of doing this digital equivalent And so now if I go ahead and click Go, amazing Like, now I get back all of the data reconstructed in a way that’s useful, because now every row contains the album ID and the artist ID, but more importantly the title of the song and the name of the artist And I now have the ability to query for the data I want without inefficiently storing all of this together And I can actually store a join on yet more tables in here, too You’ll notice that playlist, for instance, is one of the tables in here, because this digital music store supports the ability to make playlists And here we have playlist IDs which map to playlist names But how do you associate songs with a playlist? Well, if a playlist just has a name and an ID, you need some way of associating songs with a playlist by way of these IDs So we actually need kind of a many to many relationship, so to speak And that’s what a database person would say If you want multiple people, playlists, to all be able to have different songs but maybe overlapping songs, so that you can’t dedicate one song to one person, you want a many to many relationship And so if we look at Playlist Track, notice this column is crazy This column– or rather this table– has just playlist ID, track ID, whereby track ID maps to one of the rows in the track table, playlist ID maps to one of the rows in the playlist table And by using this table and joining with multiple tables, we can map playlist, which is just names of playlists and IDs, to tracks, which is names of songs on certain albums And with the right join commands, I can actually reconstruct all of the data I care about, and then in one breath get all of the data back and let the database figure out how to find all of those people and all of those songs and all of those playlists efficiently A CSV, you would have to read every darn file top to bottom and then figure it all out yourself But there are some key design decisions to be made I mentioned earlier not just these data types that SQL supports, but also the constraints pictured here So these are a few new terms that we now need as ingredients to designing a good database And we’ve seen one of these before, Primary Key It turns out that, anytime in one of these tables we have defined for ourselves an arbitrary but a unique number, generally in the left-hand column, just by convention, called ID or called Artist ID or Album ID, whatever you call it, that we’ve declared typically as a primary key This is a way of telling the database, when you design the database, give me a table with an ID column that is meant to be the primary key That is, database, I promise to you that this column will always be unique Therefore, database, use this in the future Anytime you want to do something efficient, rely on that column as uniquely identifying rows Don’t use albums or artist names and titles, which can be longer and non-unique Instead, use that, as well But there’s other fields that you might want to flag as being unique, but not your primary key, the number that uniquely identifies Like in the world of customers, which field that we played with a moment ago might you arguably want to make unique? And this was our own little database here of customers Which are the candidates for unique values here? In back? No, here AUDIENCE: Phone DAVID MALAN: No Phone, phone could be unique, certainly in the world of mobile phones But some people still have landlines That might get a little messy So maybe, maybe not What else? AUDIENCE: Names? DAVID MALAN: Names, maybe But if you Google yourself, odds are there’s many people in the world with your same name And you don’t want to have one of you only buying things from the store Yeah AUDIENCE: [INAUDIBLE] DAVID MALAN: What’s that? AUDIENCE: [INAUDIBLE] DAVID MALAN: Email probably more likely Now, we can probably think of some family members even who are maybe sharing an email address at home, which complicates even that scenario But maybe If you do at least want to mandate that email addresses be the same– I’d probably go with email over name, which is gonna collide with multiple people– then maybe we could specify yes, email address for our customers must be unique, because maybe they’re using it to log in

Right? If you’re using email as a login name, it better be unique so that there can’t be two people with different passwords but the same username or email address But an email address is kind of long Malan@harvard.edu, rob@cs.harvard Like, that’s more bytes than an integer So long story short, it might make good sense to uniquely identify your users at the end of the day with just an ID, a numeric ID, the primary key But one of the features you get from a database is you can tell the database, hey database, make this other column also unique, and make sure that I or my colleagues can never accidentally insert data into the database that is duplicated The database will defend against that for you You don’t need ifs and else ifs in your own code, you can let the database do that for you Index, meanwhile, is really powerful You can specify by the keyword index, hey database, this column is important to me I am going to want to do searches on this column I am gonna want to look up people by this column Therefore, please go ahead and index it by using your sort of secret sauce using fancy data structures and solid algorithms to actually find data efficiently And here again is where we’re standing on the shoulders of others Other computer scientists have figured out really good algorithms and data structures, usually involving trees, which we did discuss a few weeks back, that allow you to find data generally in logarithmic time, not linear time And so simply by using this keyword index, we’ll see you can tell the database use those years of knowledge to actually find data for little old me more quickly Not null You can specify don’t let null end up in this column I must have a user’s name I must have their phone number It cannot be blank Foreign key is even more fancy, but we’ve seen it already, in fact When we looked in just these examples here, rather for the songs database, notice that in Album we had two types of IDs We had album ID in the Album table associated with titles, and then each of these albums was associated with an artist ID And we’ve got two keywords here to apply I claim that this is the primary key for this table because it just uniquely identifies the albums Why is that the primary? Well, the table itself, the spreadsheet is called Album This is the unique ID that’s primarily responsible for identifying rows That key over there is not primary, because there can only be one, so it’s called a foreign key But it’s foreign in what sense? AUDIENCE: [INAUDIBLE] DAVID MALAN: Say it again? AUDIENCE: [INAUDIBLE] DAVID MALAN: To the album It’s related to the album in that it’s the artist, but it’s foreign in what way? AUDIENCE: [INAUDIBLE] DAVID MALAN: It’s in a different file in a different table So it is a primary key in another table, which is what makes it foreign if we mention it in this table, right? Strictly speaking, this column doesn’t need to be here I could just go ahead and forget about all of the values in this highlighted column But once I add it, that allows me to link it primarily to another table, because in this context it’s just called foreign keys So that’s it for the vocabulary there So what does this actually all mean? Let me go ahead now into phpLiteAdmin, which is again just our web-based tool for manipulating data And let me go ahead actually and do this Let me go ahead and close this version of phpLiteAdmin Let me go back over to my file browser And let me open up a terminal window for just a moment And I’m actually gonna go ahead and do this If I want to create a new file in Linux, the operating system we’re using, you can actually do this pretty easily by just touching the file, even if it doesn’t exist So touch customers.db, and now notice at left customers.db exists It’s just an empty file I can now, wonderfully, double-click that and begin interacting with it as though it’s a database There’s no zeros and ones in it yet until I start manipulating it, and that’s why it says no tables in database It’s just an empty file So let’s create an actual table in this database akin to what I was doing in Excel, or in Google Spreadsheets So here is where in this user interface I can create a new table on database Customers So let me go ahead and call this my Users table, because they– well, we’re just gonna call it– yeah, Users is good And number of fields, let’s just do four this time And now I’m gonna go ahead and click Go And this is again what you get from phpLiteAdmin It’s just a nice, user-friendly interface for, like, answering questions So what columns do I want in this table? Well, the first one is going to be the customer’s name, or maybe it should be first name, so like first underscore name or F Name or whatever you want to call it I’ll just call it First What should the data type be? AUDIENCE: Text DAVID MALAN: Yeah, text And then last name should be text And actually, I’m getting ahead of myself I don’t want to uniquely identify users by their names What did we decide? AUDIENCE: ID DAVID MALAN: Yeah, ID So just to be kind of tidy, I’m gonna put it first It doesn’t strictly matter, but it’s just kind of the norm So I could call this ID, as would be a norm You could call it user ID, which might be another norm, so long as you’re consistent is what’s important This should be not text but integer

And notice these other features I can tell the database this is my primary key And now no other field should be primary I can tell the database, you know what, you deal with auto incrementing this I don’t want to keep track of who is what number The database can assign an auto-incremented ID, so one becomes two becomes three over time Not null is a given, because if it’s a primary key it can’t be blank And then this is irrelevant here, but you can give default values So for instance, if, for whatever reason, people want to remain anonymous in their name, we could say, well, the default name for everyone in my database will be Bob, or something like that But that is kind of silly, as well We probably want to say that there is no default value, but rather first name and last name must not be null so that the users must cooperate and provide us with a value As for the last one here, I don’t know, we’ll just use email for now We’ll make that text And it turns out you can’t specify in the database that an email address must have something at something We’re gonna have to do that if we ever want in code But I can at least make it not null Now notice what happens Even though this is a GUI, graphical user interface, when I click Create now, I’m told this– table Users has been created But what’s nice about phpLiteAdmin, which is largely why we use it, is it’s a good teaching tool, too This is what phpLiteAdmin just executed in order to create this table So it’s a way of kind of learning SQL as you go by just click, click, clicking in a user-friendly way, and then seeing immediately feedback as to what command you could have just typed manually in order to create that same table And it’s wrapping because it’s a little long, and it’s not nicely indented But here, we have something very reminiscent of what I put on the board earlier We have an ID field that’s an integer, that’s a primary key And by the way, it should also be auto-incremented That’s a new feature But it cannot be null Over here, first name is text, must not be null Last is– should not be an integer That’s a screw-up So I should have made that text, so that’s flawed at the moment And then email all the way over here is text, not null So what does this actually mean? Well, let me show you one other way before we use this in actual code as to how else I could be interacting with this database If I open up a terminal and I go ahead and type this now, SQLite 3, which is the third version of the program, I’m gonna make this my alternative database and hit Enter This is not as user-friendly Like, I have to know what I type, or it seems I can type Help to get some help And these are all of the commands that I can type at the command line It’s not all that user-friendly But I do know this Once I know SQL, I can just interact with this with SQL commands And I’m gonna go ahead and fix this in my alternative database I did not mean to say integer, so let me make that text I’m gonna put a semicolon at the end, which is typically needed, though it’s not always needed So let me go ahead and hit Enter now Nothing seems to happen But in SQLite, if I type Schema, schema is like my database design schema means show me my database And any command that starts with a dot is SQLite specific It is not SQL, it’s now part of the language, it’s just a local command Oh, that’s kind of cool It just spits back out what it is my tables look like I can see this in another way Let me go ahead and close this for just a moment And notice that, in today’s files on the website, I have lecture.db, which was the music database that we opened up earlier Let me look at that SQLite 2, lecture.db Let me do .schema Oh That is all of the data inside of this database redisplayed to me as text So I can kind of back it up if I want, or do other things But let’s just skim it for something interesting Let me go to the top And here we have, nicely formatted, these are the commands that the people who wrote this database online from whom we downloaded it essentially made Create Table, Album The square brackets are optional, but they just are alternatives to quotes One of the fields will be called Album ID, Title, Artist ID We’ve seen those things before One is an integer, one is text, another is integer And then we’d see these features here, which we didn’t see earlier And I’m gonna wave my hand at them for now But there is a way to tell the database that there is indeed a foreign key So you can say, in this table, there’s a number that refers to another table, and you can let the database make sure that you can never add an artist ID that doesn’t map to an actual artist So you can’t just insert artist number one, two, three if artist number one, two, three doesn’t even exist So again, another feature you get from the database And for PSET 7 we’re just gonna scratch the surface of some of these But again, this is one of the reasons that you sort of graduate from text files and you graduate from [INAUDIBLE] and start using [? full-pledged ?] database software like SQLite or Oracle or other such tools So let me pause to see if there’s any questions before we dive into Python All right, so let’s now actually use this in code The value of seeing, hopefully, SQLite 3 and in seeing phpLiteAdmin is just so that you have tools with which you can create your databases and actually see what’s inside of them without having

to write code to do both, which just makes it very hard and annoying to get started So phpLiteAdmin is what you’ll use for problem set seven And we’ll end with a look at that in a bit But it’s just a way of administering your database It’s not actually the intellectually juicy part For that, we’re gonna need some actual Python code So for that, let me go ahead and open up my console again Let me get some tabs open here And let me go into let’s say Lecture 0 folder from today And this is not online now, but soon will be Let me open up application.py and see what this app is Super simple This is just copy paste pretty much from past examples And notice that it has one route, slash, that’s gonna render index.html And then what is that template? Well, index.html is one of two files My layout is big generic HTML structure of the page Index.html looks like this It’s a big to-do So let’s actually do something with this program as follows But first, let’s take a stepping stone Let me go into New File, save this as lecture.py It’s just a local script, nothing to do with web programming just yet And let me go ahead and do this First, for those in the room who’ve never seen Python before today, python lecture.py– that is a program written in Python If you’re underwhelmed, that’s deliberate at this point It’s a one-line program But now we can do something so much more powerful Let me go ahead and essentially do this In pseudo code, query database for all albums Whoops, albums For each album in database, print title of album Like, suppose this is now the program we want to write How do I do this? Well, in Python, I’m gonna go ahead and do this From CS50, import CS50 SQL library, which will be introduced again in PSET 7 And it just makes SQL a little easier to get into And for your final projects, you’re welcome to drop use of the library all together It’s just training wheels And I’m gonna do this DB for database, but I could call that anything I want, is going to get a SQL database whose file is of type SQLite colon slash slash slash just because dot DB So long story short, this one line of code just says, hey Python, open a SQLite database called lecture.db that’s in the same directory That’s all So after that, I have a Python variable called DB And this is a variable that allows me to talk to the database in code And this is the missing piece So far the only way we’ve interacted with lecture.db thus far– and lecture.db, again, is the musical database– is by using phpLiteAdmin, which is just this administrative GUI, or SQLite 3, which is the command line version of the same We haven’t actually done anything intellectually interesting by querying for data in a programmatic way I’ve done it all very manually But not now Now let me go ahead and do this Let me say, go ahead and store in a variable called Rows All of the rows in the table that are the result of executing, select star from album So I’m writing Python code, but inside of my Python code I have a string, and that string has SQL in it So what do I now want to implement this pseudo code? So that’s what I’ve just done That’s my comment Now I want to iterate over each album in the database How can I do this? Well, let me go ahead and do that So for each album in database, so for row in rows, old syntax by now, what do I want to do? I want to just go ahead and print out row– what were the columns in my album table? AUDIENCE: [INAUDIBLE] DAVID MALAN: Yeah, it was album ID and? AUDIENCE: [INAUDIBLE] DAVID MALAN: I think it was title in the album one So I’m gonna do this Quote unquote– I think it was called title We shall see if I’m remembering correctly And now let me just put my comment over here, as well So now we have a pretty short program that just executes a query, and it turns out– oops, I indented excessively– this variable Rows contains what’s called a result set It is a list, an array, a list of all of the rows that match that query, which hopefully is all of them Then this is just Python from a week or two ago for each of the row in that Rows list, print out that row’s title Now what is this exactly? You can think of this as a hash table, or a dictionary Row is one of the rows It has a bunch of columns And the syntax in Python for indexing into those columns is just square bracket notation This is a dict, or a dictionary object in Python OK So let’s cross my fingers because I’m not sure if I named everything correctly here and do Python of lecture.py, enter Whew It worked So outputted here is every title from the database So let me go ahead and just pick one For instance, a soprano inspired And just do another proof of concept

You know what? I don’t want to get all of them I want to go ahead and get where title equals And now I will use quotes to distinguish it So how many rows should this query now return? AUDIENCE: [INAUDIBLE] DAVID MALAN: Hopefully just one So let’s see Let me go ahead and rerun it, lecture.py And very nice And now two things printed, though, which we didn’t see before because there was a lot of output, but it was there It turns out that the CS50 library, to help you out, especially with problem set seven, always prints out this debugging information that you can technically disable It’s not part of your program, just kind of sent in addition This shows you exactly what query was executed, which is gonna be helpful because, when you start using web programming, as we’re about to, as well, you can then see in your terminal window what your web app is actually doing to make sure your can diagnose any bugs in your code But we can be fancier than this Remember for problem set six, or soon see in problem set six, that if you import sys for a Python program, this gives us access to what, among other things? AUDIENCE: [INAUDIBLE] DAVID MALAN: Yeah, command line arguments So what if I do this, where title equals something I want that to come from the command line So how can I go about doing this? Well, turns out there’s one other piece of syntax that you need to know from the CS50 library The Execute method supports what are called name placeholders, where you can essentially say this, colon t, or colon title, or colon whatever you want It’s just a placeholder And I’m just gonna call it t for title And what you can then do is this You can specify that the value of t shall be, for instance, sys.argv bracket 1, which will be the first word that the human typed in at the command line And what is Python gonna do? It’s gonna plug this into colon t That’s all It’s just a placeholder And just as an aside, we’ve seen this before Let me delete this for just a moment As of like two minutes ago, the way you might have done this is like this, with a so-called f string or format string, just plug it in This is dangerous, for reasons we’ll see in just a moment So I’m gonna actually do this instead, the colon approach, and let the CS50 library actually scrub or sanitize my data so that the user can’t accidentally trick me into, like, deleting all of my data So now let me go ahead and do this Python of lecture.py– whoops Python of lecture.py And now let me go ahead and type in that same title, which was– what was it, a soprano? AUDIENCE: Soprano inspired DAVID MALAN: A soprano inspired? Inspired? And now I’m using quotes Why? AUDIENCE: [INAUDIBLE] DAVID MALAN: Yeah, it’s one phrase I want it all in argv1 I don’t want it partly in argv1 and 2 and 3 It’s just gonna be messy Enter Whew It actually worked So now I’ve written an interactive program that takes user input at the command line, plugs it into the SQL string, and actually allows me to get the data that I care about Now, why did I do this? As a quick aside, if you do it this way, which would have been our technique as of, again, like minutes ago, before seeing this, this technique– and actually, now I need to quote it inside the SQL string, the library otherwise does that for me– this is actually very dangerous This is violating a principle that henceforth you should be very mindful of Why might this be dangerous, just plugging in the command line argument to that string? AUDIENCE: I could put malicious quotes, or I could put, like, [INAUDIBLE] DAVID MALAN: Exactly You can plug in sort of code that you didn’t intend So let me go back down here What if a really malicious user didn’t just type in the beautiful name of a song, but did something like haha semicolon delete from album to try to delete your whole database This isn’t perfectly right This won’t actually execute properly Haha is not a keyword But the semicolon means that’s it for this query, the delete from album is a valid query that we saw before, and this is what’s called a SQL injection attack And in fact, this is very common even today, because one of the features you’re getting from the CS50 library is sanitization or scrubbing of the user input And the takeaway for today is that you should never henceforth trust the user’s input ever, even if it’s you Get into the habit now of distrusting even yourself, lest you somehow inject data into your database that shouldn’t be there Let me demonstrate this as follows So if you’re logging in, for instance, to a Yale University website, your login form looks like this Or at Harvard University, it looks like this, Harvard [? key. ?] Now I’m gonna g sort of reveal what a password prompt actually looks like without showing the bullets, which is the norm, but in the context of the web, if a user were to type in seemingly dangerous instructions, maybe this could happen So suppose that my name is me@exampleprovider.com or whatever, and my password is this funky single quote or quote unquote one equals one Like, this is not a complete thought

But notice it’s miss– it ends a quote here, and it lacks one here So it’s kind of like this partial thought, because if an adversary out there on the internet is trying to hack into a Harvard or a Yale system or the like, he or she might think what if someone at Harvard or Yale wasn’t so bright and did not sanitize users’ input, and just trusted whatever it is Maybe I, the malicious adversary, can try to finish their thought, so to speak, in a malicious way And what does this mean? Well, suppose that the code that someone at Harvard or Yale had written looks like this Give me a user name variable and a password variable using syntax that we started seeing last week, and you’ll see more of in problem set seven This just says get me whatever the user passed into the web application, as opposed to from the command line And then suppose that the Harvard or Yale staffer actually executed select star from users where username equals this f string syntax What’s interesting here is that they are correctly quoting whatever the string is supposed to be for both username and password But remember what the user was about to type in a moment ago If I kind of close one quote and then like leave one open, I can kind of inject arbitrary statements in here And so what a SQL injection attack might look like is this If I type in exactly those values, me@exampleemailprovider.com, and that funky password, notice if you look past the syntactic weirdness what really this is saying Where password equals quote unquote, which is kind of silly, if your password is blank, or if one equals one Now, which of those statements is obviously true? One equals one And this is arbitrary I could’ve said foo equals foo or anything The point is, I sort of finished one thought with that single quote Then, I typed whatever I wanted because I’m presuming a little cleverly, but also kind of guessing, because the adversary can’t see this code, I just have a hunch that, if I construct the right query, I can trick the database into returning any user because one always equals one So I’m gonna get back a row which is probably gonna let me into the system So what the end result is is very bad, unless you sanitize the user’s input So this other approach with which I started, actually using these colon-based conventions, which is very similar to percent [? s ?] in C, but it’s just now SQL-specific, or SQLite-specific, this now ensures that when the user types in even the most malicious-looking data, CS50’s library, or other libraries out there in the world that do the exact same thing, is gonna make sure that any dangerous characters like apostrophes are escaped with backslash So long story short, you don’t even have to know or care what a SQL injection attack is fundamentally if you just practice good database sanitization techniques, which doesn’t sound very cool But it’s very easy to do so by using, in our case, the CS50 library, but in the more general case, actually using a library like it And so this is kind of a fun meme that kind of goes around the internet, supposedly like some actual person’s car This was meant to do what, do you think? AUDIENCE: Hide the license plate DAVID MALAN: Well, not just hide the license plate This is probably one of those cities where you actually have cameras overhead that are using optical character recognition to bill the right person for their license plate, or ticket the right person for their license plate So this person, correctly or incorrectly, was presuming, hey, maybe, you know, the city of Cambridge has some buggy software that’s not sanitizing its inputs like that And before we look at an actual web example, I would be remiss if I didn’t teach a generation of aspiring programmers this comic [LAUGHTER] So henceforth, if you ever hear about Little Bobby Tables, this is what CS people are actually referring to So let’s actually now use this in a web context So I’m gonna go ahead and do this I’m gonna go back to the IDE, close out the command line program that I was writing a moment ago And let’s just fill in this to-do Let me go ahead and go back to application.py, which doesn’t do much of anything just yet But I’m gonna go ahead here and do the following I am going to initialize my database So this gives SQL of SQLite colon slash slash lecture.db, so just like before And then in my index route, I’m gonna do something like this, rows gets db.execute, which is the only method in there that you need to know Select star from album And that’s gonna get me all of them And now I’m gonna pass to this template all of the rows And I’m gonna call it albums, and I’m gonna pass in all the rows And this is a way now of handing all of those rows and those titles into my template If I now go to my template where I have this to-do, I can use some Jinja syntax, which honestly is not going to sink in until you start practicing it some more, but I do know from experience it’s for album in albums Now close brace, and then endfor is gonna

be how we end this in my Jinja template And in here, I’m gonna go ahead and output each time the album’s quote unquote title, if I didn’t get a syntax error And so now, let me go ahead into my terminal window and do in my source directory in my lecture example flask run to start the web server Open up the URL Whoa So that’s kind of a mess But it kind of looks like all of the titles, probably Let me clean this up And here’s where HTML now becomes really useful In HTML, what’s the tag for, like, an unordered list, [INAUDIBLE] list? AUDIENCE: [INAUDIBLE] DAVID MALAN: Yeah, UL for unordered list So let me go ahead and make an unordered list tag there, but close it after this loop Let me go ahead and nicely indent everything And now inside of the loop, let me go ahead and output a list item, recall from a few weeks back And now it still looks super cryptic, but what is the logic saying? Within the body block of my page, go ahead and start an unordered list Iterate over each of the albums, and then output its title inside of a list item So if I go back here and reload, what should I see? A whole list of those album titles Moreover, I can do a little bit better Let me go ahead back now here and do something like this Let me go ahead and say q equals request.args.get q And q is, again, what Google uses for its queries, but I could call this anything I want Let me now go ahead and pass in the following Where title equals colon q, and then let me pass in q equals q This feels a little silly, but just think about what it’s doing I want to substitute for colon q the actual value of q And maybe I can be more explicit here If I call this query, what I’m really doing here is passing that in to there So now that I’ve done that, what can I do? This is not a very fancy web application, but suppose I want to search only for what was it, the soprano song before, soprano inspired Actually, let me search for something simpler How about just worlds A la hello world Let me go to my URL And remember, if you understand HTTP, we can do question mark q slash question mark q equals worlds, and enter And dammit Where title equals worlds q– where title equals worlds Why did you not come out? request.args request Stand by real quick We were doing so well there All right, let me just reload Oh actually, what a good time– none OK, none is bad What am I doing wrong? Did I screw up? OK, hang on Stand by What a good time to introduce eprint for just a moment from earlier Now let me go ahead and reload the page to trigger the load OK, that’s bad Dammit eprint is not defined from CS50 Oh Oh, so close to– yes! OK [APPLAUSE] In fairness, I have no idea what just happened But it worked So let’s now, with those inspiring words, give us a sense of what it is you’re going to be doing with these ingredients We focused a lot today on SQL and on database design, but the ultimate goal at hand is going to be to use these ingredients and use this new language to query for real information So here is what we call CS50 Finance, AKA problem set seven And you’ll be given a little bit of distribution code that essentially gives you a user interface similar to this that just allows users to log in and log out But it doesn’t allow users to register It doesn’t allow users to buy or sell stocks yet, as will be the goal CS50 Finance is like building your own e-trade website where you can buy and sell stocks But it’s going to integrate thanks to code you write with Yahoo Finance, which happens to provide essentially a very lightweight API, application programming interface, whereby if you ask Yahoo for the current stock price of a certain stock ticker, they will respond and actually give you that answer so you can integrate it into your website So what you’ll ultimately be able to do– and this is simply the staff solution– is build a website like this one here, such that I can go ahead now and log in to CS50 Finance as a John Harvard with his password I can then see that, by default, all of your customers, wonderfully, for a special deal, are going to get 10,000 virtual dollars I can go ahead and get a quote for a stock, like GOOG is G-O-O-G Google as of right now is just over $1,000 That’s great, because I have $10,000 So I’m gonna go ahead and buy one share of GOOG by typing in its stock ticker

symbol and one And now I actually have one share of Alphabet AKA Google plus some remaining cash So all of this and more you will implement Happy to stick around for questions That is it for today And thank you so much for today’s parents and families for coming [APPLAUSE]