Full Stack for Front-Ends Part 2 Dabase Best Practices

[00:00:00]
>> Jem Young: I have some database tips for you. First one, back up your database. You'd be surprised the number of businesses that have lost money, gone under, just made really silly mistakes cuz they didn't backup their database. It's kind of simple, but yeah, anyways. Use a strong root password.

[00:00:15] Root is all powerful, just like on your computer, same thing. Someone who logs in with root, they can just delete all your data. They can change it as they want and they can add $10 million to their account. Doesn't matter, use a strong root password. Generally add users, but give them fine grained permissions.

[00:00:32] So most users don't need the write ability, they don't need the ability to write to a database. So throw that in there, you create users that have permissions, just like we did with chmod, we can do that with SQL. The syntax is different, but the idea is the same.

[00:00:47] Limit isolation of users' powers is always a good idea, especially with databases. I read a story on Reddit. It was a couple months ago, but it was some guy, he just started a job and he deleted the production databases. Some people remember that story. And I felt bad because it's not his fault.

[00:01:06] It's not his fault that the permissions weren't set up correctly, that he had the ability to do that. Again, just like chmod, just like running things in sudo, if you give people power, they will probably shoot themselves in the foot eventually. So we wanna limit that as much as possible.

[00:01:19] So, don't give people power to, A, delete anything. That should always be root. Don't let them have the ability to overwrite data, the same thing. Yeah, I feel bad for that guy, poor guy, cuz it wasn't his fault. How would he know? Also use a dev database, and use production database.

[00:01:41] Do not, if you have a dev environment, do not use a production database. These things are common sense, and people in the real world are shaking their heads. But yeah, people do it, you'd be surprised. Databases will put you under very quickly, if you're not just very cautious with them.

[00:01:57] Don't expose your database outside the network. There's not many reasons why I would be need to be able to log into my database from a browser. So in general just don't do that. Only within your network of computers should you be able to log into a database. That's an easy one.

[00:02:11] Again, people don't do it. They expose their database to the world, people are gonna try to hack it. Even if it's an empty database, people just wanna know what's inside. Fourth one, sanitize your SQL. So what that means is, if I can run random commands from my web app, as in it's unsanitized.

[00:02:30] So I can say, drop tables dot, and I have that ability from my web app, because you didn't do permissions correctly, you're not sanitizing your SQL. That's a very, very, very common vulnerability. If you're talking about any sort of website penetration testing, application testing, the first thing they're gonna do is SQL sanitation attacks.

[00:02:50] Wait, I think there's a about it.
>> Speaker 2: Bobby, drop tables.
>> Jem Young: Bobby, drop tables. Xkcd SQL,
>> Jem Young: Little Bobby tables
>> Jem Young: [LAUGH]
>> Jem Young: [LAUGH] Love this one. And now we understand a little bit more about what this joke means. You have to sanitize your SQL so one, people don't have this ability.

[00:03:18] Two, they shouldn't have this ability anyways, as a random user, to drop tables. Drop tables just means delete the whole table, people do that all the time. It's like basics, which you're not gonna do. Cuz you now learned, that I just harped on that for the past three minutes.

[00:03:34] The way to get around sanitize your SQL is almost every library. So we can use node SQL, node by SQL miss time has a way of sanitizing database or SQL queries for you. People try to do it by hand. They say, I'm just gonna pipe whatever the user sends directly into the database.

[00:03:51] That's where you run in trouble because there's always something you haven't accounted for. So in general, don't put a pass raw SQL into your database. And if I'm saying things that are kind of nuanced and kind of fine-grained, if you've ever worked with any sort of database, you probably know what I'm talking about.

[00:04:08] If you will, remember my face at this moment, sanitize your SQL. And the fifth tip is backup your database. Yes it's in there twice, because people don't do it. At some point someone might drop your database, your database might get corrupted. Someone did a mass update and they just rewrote all that data.

[00:04:24] It happens all the time. You need to have the ability to roll back. This is important. These are more production tips, which you wouldn't run into locally, but backup your database. I actually had another one in there but I took it out cuz I thought it was a bit much.

[00:04:37] But I just want to make sure everybody understands my point. So if we wanted to, we can hook MySQL into Node. We're just going to use the library of MySQL JS. We're not gonna do it because, it's a bit late in the day and we don't really have anything to query.

[00:04:52] But if you wanted to, it's just MPMI, MyAQL JS. Goes through everything on how to set it up, how to use the user, using a correct database, how to make queries, things like that. If that's what you're interested in, cool. I think there might be a frontend Masters course which goes into a bit more detail on databases.

[00:05:09] And if so, I'll try to link it at the end of the talk, if I figure it out. But anyways, that's MySQL. That's node. That's setting up a database in general. That's database at a very, very, very high level. Again, it gets so nuanced. You have like things like Redshift or Cassandra.

[00:05:25] You have different types of database for querying at different speeds. Different pros and cons, but I won't go into that today. This is just full sets. That's all you need now, for now.