 Welcome to this video from FilmsByChris.com. That's Chris Backe. I am Chris. And today we're going to be playing around with SQLite files, SQLite 3 files. This isn't really, this is part 4 of a series. If you're really interested in SQLite 3 databases, those first two tutorials are the main ones you want to look at. The last one was on using them in scripts. Today we're just going to be looking at the files. We're going to talk about it. This isn't really a tutorial, but more of just talking about SQLite 3 files. Basically you have lots of options out there when it comes to databases. And one of the most popular out there is SQL, which is done created by Microsoft. And then there's MySQL and SQLite. And when should you use these? Well, personal opinion, SQL, never, because one is made by Microsoft and it's proprietary. When it comes to the other two, there are times where you want to use MySQL and times where SQLite is a better option. And where would those be? MySQL is great if you're creating a web server with a web interface and you're going to have databases on there. It's really good, you know, as your databases get large. SQLite is great if you have an application, maybe a small database, again I've mentioned on your phone, if you're running an Android phone, I have no clue about iPhones and how they store stuff, but depending on what applications you use, your contacts or text messages, all that stuff, it's probably stored in a database on your phone and there's a good chance it's an SQLite database. And that sort of thing, small databases for applications on your local machine where MySQL is better for server stuff and web stuff, but also size. I read somewhere that the creators of SQLite say it's going to be a huge database. You do not want to be using SQLite. Once it gets big, it gets just not fast enough where MySQL might be a little bit faster. Commands between all three of these programs are pretty much identical, so if you know one, the other is just whatever application you're using to write to them. But I wanted to show you something really quick. Let's say you know absolutely nothing about SQLite databases and you want to get information from them, maybe you're just pulling stuff off your phone and you don't want to go in and install SQLite, maybe you're on a very light system and don't have it installed or you haven't installed, but you don't know how to use it. Everything is pretty much stored as plain text in there. So again, if I list out here, I have three files, one is script we made in the last video and then there are two databases and if I list these out, you can see one is a shell script, ASCII text and the other two are SQLite three files. Well, if I was to cat out one of those files, for example, example two dot DB, you can see some of our information in there. It's kind of hard to read and some of them might be a little messed up, but you have our entries in there. So again, if I was to run an actual SQLite three command, you can see this is what our database looks like and you can see some of that information in there. Not all of it, some of it's messed up and missing. But there is a command that is probably on your Linux system already. It's fairly common and I've talked about in the past, it's called strings. Whoops. And strings is great when you're backward engineering stuff to try to pull apart binary files because what it does is it ignores all the binary information and just shows you plain ASCII strings. So I can point strings at our example two dot DB and it's going to, not perfectly, but much clearly print out our database. So let's say, like I said, you pulled your contacts or your text messages off your phone and you're looking for a certain text in there. You're trying to find the date that it happened or something. Well, if you were to, if you knew SQLite commands, you can obviously open the app and search through that way. But if you didn't or you haven't used them in a while, it's very easy. You can do strings and then you can grep for it. So in this case, I can say like smith and it'll grab everything that smith. But another option, let's see how this looks. If you were to grep for smith directly to that file, you're going to get an error here, I believe, that it's a binary file. Well, you can actually grep through binary files by saying dash A. So dash A is saying that this is a binary file, just grep through the ASCII characters of that. But you still have that same problem of just cutting it out where it kind of, it's all on one line, it's kind of messed up. We're using the strings somewhat. Format's a little bit better in the case of these databases. So again, I just wanted to point this out that even if you know absolutely nothing about these databases, if you're familiar with what the string command does, which I just told you it does, it just brings out strings from files, whether the file's binary or not, examples two, example two, again like that. And then you can just grep what you're looking for. I can find Jack in there, and there's Jack's and his phone numbers. Again, not perfectly formatted, all columns are kind of pushed together. But that is a quick and easy way to look at these files. So I just wanted to bring that up because for me it's useful. Now, another thing I want to bring up about SQLite, let's say you did create a web server and you wanted to use an SQLite file, you could. I have done that. You can use PHP as a programming language to access these. You have to be very careful, though, about this because if you put the SQLite file on your server in a web directory and it's readable, well, now anybody who knows the URL to that database, which they might not be able to know because you do it with server-sized stuff, you may not know it. But if somehow someone was able to find it, it somehow came up somewhere, they can pull down the whole database with the URL HTTP request, either with Wget, Curl, or just through a web browser. So you want to make sure that it's either set through your, like if you're using Apache, you can say that this file is not accessible from these, or even better yet, don't put it in your web directory, I'll have it in another place on the server that is not part of your web server. That's something, now, if it's something that it's all information that you want publicly available and you might want people to download the database, like it's just public information that you're putting out there, and maybe you have code to display it properly on your web page, but you want to give someone access to download, that's great, because you don't have to do anything other than give an href link to it, and people can download it, and there you go. But definitely a security issue there, where if you're using MySQL, it's set up as a server, it's password-protected, knows these databases aren't password-protected, where your MySQL isn't in your web directory, it's a separate server, and when you're actually doing a request to it, it's actually using a whole other port to read, and you've got to log in with the username and password to access that information. So there are some thoughts. These SQLite databases are great for applications, where security may not be a huge issue. If you are doing it with something in security, be sure, be very careful about how you use it. Definitely bigger databases, and if you're doing a web server, MySQL, in most cases, is going to be a better option. Anyway, I hope you enjoyed this. Again, it's just more talking about how these work, places to use them, how to use them. Check out those previous videos if you want to learn more about the language in general. Very, very basic. You can actually get more in depth, but 99% of what you're going to do are those commands I showed you in the previous, creating database, creating the tables, inserting information, querying the information, deleting rows, and updating the information. So if you want to update, and of course, also altering if you want to add a column. But that's going to be 99% of what you do with these databases. And that's it. I do thank you for watching. As always, filmsbychris.com, it's Chris Decay. There's a link in the description. That is my website. Go there to search through my videos. I thank you for watching, and I hope that you have a great day.