great to see folks logging in from around the world
Alright, so welcome again, everybody. Glad you're here. Good to see everybody popping into the chat area. Folks logging in from many countries around the world lots of places across the United States as well. Kathy Zant is us as with us today, we're talking about cleaning up a hacked WordPress website, focusing especially on the database. This is the second part of two Kathy did the first part about this time last month and I'm looking now to find the original webinar there
so we're gonna get started in about five minutes from now.
Here it is. All right.
So if you missed the previous webinars, save this link. I just dropped it in the chat. Go back and rewatch that webinar and lots of great information on identifying things that might be arrived with your WordPress install. So again, if you're just joining us in zoom, pop, open the chat window and say hi, tell us where you're logging in from today. Ashton, welcome from Zimbabwe. I think this is the first time we've had someone join us from Zimbabwe. So welcome, Ashton. Glad you're with us. John, from Titan. Is that Teton? John? I'm not I'm not quite sure where that is. Tell me. I'm interested. Welcome Christian from Argentina. Luca from Italy Richard from Oklahoma City Randy from New York. Doug from Toronto Sophie from Amsterdam James from England Heather from California. Dominic from Germany, Nikki from Illinois. Robert welcome from Toronto Jeffrey from Washington. welcome Karen from Tennessee Ben from Sweden, Sally from the UK. Welcome, everybody. Glad you're here. We're about four minutes away from getting started with this webinar all about cleaning up a hacked WordPress website with Kathy Zant focusing especially on the WordPress database today. There is no handout this is all live demo welcome Phil from the Isle of Man. KENNETH from Florida, Janice also from UK Phil from North Dakota. Welcome everybody. Welcome BG from Germany. Oh from Italy. I read that too quickly. Bg. Welcome Stacy from Colorado. Ricardo from Michigan. We're about three minutes away from getting started with Kathy Zant. Part two of cleaning up a hacked WordPress website. If you missed part one here's the link you can go back and rewatch that event
again, no handout or downloads today it's going to be all live demo taking a look at the WordPress database and noticing things that might not be quite right so we will be using the q&a feature
interestingly, the does anybody else not see the q&a link or is it just me I'm not seeing a questions. Button. It is missing. Fantastic. Well, let me see if in the next two minutes I can solve that. It may be too late since we launched the webinar. And if that's the case, we'll just use the chat to ask questions.
Yeah, I can't fix it now. I believe it apparently this webinar wasn't set up with the q&a enabled. So we'll just use the chat area for questions.
Sorry about that.
Stacy, the link I just posted was for the previous webinar. This is part two in a two part series. There was a there was a resource download in the first webinar. This is especially looking at the WordPress database, no particular
handout today. handout from part
one will be helpful today. So if you haven't gotten that one part of that does talk about some database cleanup. So that is useful.
Alright, so if you need that part one handout, I've just dropped it in the chat. I'll be sharing that again throughout today. We're just about ready to get started about a minute to go.
Welcome, everybody if you're just joining us in zoom, we're about to get started just the less than a minute to go. We're on part two of how to clean up a hacked WordPress website with Kathy Zant. We're going to be focusing especially on the WordPress database today. It's all live demo no download although we have shared I'm gonna share once again the link to download the handout from part one. We're just about ready to get started here. We will be using the chat for q&a. This webinar for whatever reason didn't get set up with the q&a box checked. So we don't have that feature for today. But we can just use the chat for that just about ready to get started. Welcome everybody. Good to see folks logging in from around the world today. Lots of countries around the globe represented as well as lots of folks from the United States and Canada. Good to see everybody here. All right, I've got three minutes after so I'm going to start the recording and that'd be right back with Kathy. Good afternoon, everybody and welcome to another Live I iThemes Training event. My name is Nathan Ingram. I'm the host here at iThemes Training and I'm joined today by Kathy Zant. Kathy is the marketing guru on the Kadence team here at AI themes and she is also a recognized expert in the WordPress security area. Kathy has worked with lots of companies in the WordPress space and has quite a bit to share about security as well. So welcome back I iThemes Training Cathy glad you're here with us.
I'm so happy to be here and I'm so happy that all of my experience cleaning up hack sites is now useful for other people.
This is really it's kind of where you got your start in the WordPress space isn't it Kathy cleaning up word hacked sites?
Well, my the story of how it started I had been using well I was aware I was a developer before WordPress even came along. I had my own little CMS that I had written up for, you know, my mom's site for blogging for myself that type of thing and when WordPress came along, it was using sort of the same database classes that I was using. Because I was of the mindset that databases and web scripts belong together. And because WordPress was so familiar to me, I kind of got into that and but I didn't really professionally get into helping other people until I was homeschooling my daughter, excuse me, and I needed something to kind of keep me engaged. And right same as saying I needed a grown up language and that eighth grade math. And so I was kind of cleaning hap sites on the side helping my husband with his business and I got kind of sucked into this whole security world. It was so exciting to me and one of the reasons why I really liked it was because no two hype sites were ever the same. There was always something new and unusual and very creative methods of not only getting into a site but also the types of things that hackers were doing once they did get into a vulnerable site. So I liked the creativity. And I also liked being able to show people that security isn't something that only experts know IT security isn't something that is you know, behind closed doors at DEF CON security is all of our responsibility. And so that really inspired me to kind of you know, step out into going to word camps and educating people so that they could feel empowered and not afraid of what pesky little hackers were up to
fluidly. So Kathy's been very generous sharing her knowledge about security here on a couple of webinars, the part one replay I've just posted a link in the chat. If you missed that one from last month you can go back and rewatch also there's no handout today it's all live demo, but there's some information in the handout from part one that might be helpful and drop that link in the chat as well. A couple of other housekeeping notes as we go. We made a mistake when this webinar was set up and the q&a box wasn't ticked. So we don't have the q&a feature available for us on this webinar. So we'll just use the chat to ask questions. There are a lot of folks on today. So one thing that's very helpful especially for me, as I'll be pulling those questions out of the chat is to if you have a question, write the word question in all uppercase at the beginning of what you type that just catches my attention to make sure I don't miss your question. So question and all uppercase, and we'll be sure to get that saved for Kathy as we get toward the end of today's event. I believe that's just about it. So Kathy, why don't you share your screen one other issue. We do have some of the the the browser window zoomed in a bit. But if you if you're on a small screen and you can't see the screen very well there's a view options. Drop down at the top of your zoom window once the screen start sharing, and you can actually increase the size of your screen there just for you. So with that, Kathy I'll turn it over to you and let's talk about WordPress database hacks.
Sounds good.
I'm excited to be here. And the last time we the last time we did our session, we
we got pretty deep into a lot of different things. Obviously there's a lot of things that go into WordPress there's a database and there's files and there's FTP and there's ssh, ssh and secure certificates and user logins and there's so many different kinds of doorways into this technology. We got kind of into the weeds on a lot of things here. We're really going to focus on the database. And what I'd really like to do in this session is to talk about what the what the database looks like, what how databases actually work, what's going on. Why is there a database, all of that fun stuff? Because you know, the thing is, I was never really the security expert. I mean what back in the day before I was doing WordPress, I was working in a network department and I was kind of the web person at that all these network people didn't really understand what I was doing there. And I inherited these servers that they had set up somebody had set up and one of the servers wasn't fully secured and it got hacked by a white hat hacker who left me a little note saying the server needs to be secured and that kind of like threw me into the deep end of what security was all about. But I was never like the security expert when it came to WordPress. But because I understood how WordPress was structured, all of the moving pieces that were happening under the hood it was kind of like I grew up with with the car and understanding you know that there was an engine and a carburetor enough don't ask me about cars, but I understood what went into the engine of WordPress. And so because I understood that when I started cleaning hacked sites just kind of on the side, kind of just for fun too. So my mom had something to do, because I understood what pieces belonged there. When somebody you know, moved the giant purple sofa into the living room of WordPress, I could identify it very clearly that that does not belong here and I could look at that much more in depth and that made me good. So in order for you to get good at cleaning hack sites for you to understand security, you have to know what furniture belongs in the house and what things go together. You know that the purple sofa belongs upstairs in the media room and not in the living room. Those types of things that once you understand the lay of the land, it becomes very clear. I was just talking to Nathan before we started and he said something about how they taught taught people who were who were experts at identifying counterfeit bills. You know, like dollar bills, 100 bills, whatever, that they were able to identify those because they looked at so many $100 bills that they could understand then when something was just a little bit off and to with editing. If you're editing a document you get really good at understanding you know what word is misspelled even without all of the help that we have now from technology, because you understand the English language really well. When you understand the tools that are before you and something goes wrong. You are more able to identify it. So today we're going to focus really on that we're going to focus on what is happening under the hood with WordPress as it relates to the database. Last time we talked a lot about the file system. And you know, just generally speaking when you're dealing with a hacked site, the file system is going to be I've never seen just a debt well no I take that back. I have seen just a database get hacked, but that's because the database wasn't fully secured. But most of the time when you see a hacked site, you're going to see more malware in the file system. As well as the data like if there's going to be one or the other. You're going to see the file system polluted more than the database interestingly, that hacked site that was just the database was a Justin Bieber fan site. And it was gigantic. It was 50 gigabytes and trying to clean that up. That was a load of fun, but it was just the database and then there are PHP myadmin, which we'll talk about in a minute as well wasn't secure and that's how the hacker got in. That was a really tricky one to figure out. So let's talk a little bit about WordPress. If you're new to WordPress, and you've just like installed it and your hosting providers account, it might just look like there's this magic happening and you have this web based interface and things are going on and you don't really know what's happening under the hood kind of like you just get into a brand new Tesla and you're driving it down the road but you have no idea that technology that's making that happen. If you're owning a WordPress website, it's good to at least understand you know that there are pieces to the puzzle that are important. So WordPress is composed of PHP files. There is a PHP interpretation engine that happens on your server. And those PHP files talk to a database that typically that database has been a MySQL database, but WordPress can talk to any kinds of databases. Maria is kind of like another flavor of MySQL and other open source database system. But I've seen people install WordPress and Microsoft servers with SQL Server as the database engine. The cool thing is, is that most databases don't know if I can say all every database that I've encountered is structured pretty much the same way. And php. If you tell it how it communicates. You can see how
it can see what's in the database. It can see what's there. So what is stored in the database versus what is stored in files. Typically speaking, content is what is stored in the database. So your posts your pages, some settings some basic settings, some settings for plugins, which plugins that you have installed, um, you start adding plugins and they start adding their own information into the database and then PHP is just reading all of that content. But typically speaking, if you go back to the history of WordPress, it has been content in the database and the scripts of PHP that interpret what's happening with that content and displaying it so you have like your design files and your theme. Those are all stored in the file system. So basically speaking content in the database and all of your structure that interprets where that's going to be shown to a user and how it's going to be shown to the user that's typically been in the file system. And of course, WordPress is so flexible. We can do so many different things with it. A lot of times you'll get plugins that are storing tons of content and files file system and tons of themes that are storing more information into the database and that type of thing. So it's not like a set in stone that that's how it is But typically speaking contents in the database, and our structure and how the pages are laid out. Those types of things are stored in the file system. So what does the database what does the database actually look like? Let's um, we have a little test site set up here on our Nexus portal, and Nexus gives you PHP myadmin, which is a tool. It's basically just like another CMS except it basically shows you it shows you the content in the database in a way that is very stripped down. There's no color and pictures or anything like that. So here we can take a look at what PHP myadmin looks like. And this is basically giving you sort of another way to address the database. This isn't the only way to address the database obviously your your WordPress scripts are inserting, updating, deleting data, all from scripts, PHP scripts. This is also written in PHP, but it's going to show you more information about what's in the database. So if we see over here, let's see we'll load this up. These are tables. So we have and they all kind of start with WEP although sometimes you might have a hosting provider that's going to you know, change that WP underscore into something else because there used to be like the security myths that if you change that then none of the hackers scripts are going to work. Well that's not necessarily a few station of where information is stored is not necessarily going to keep you safe. But anyway, we have WP underscore which is traditionally how WordPress has worked. The big things that we're looking at is our posts table. Our WP options table in our users table. And then of course there's some meta information around all of this. So we have things like our taxonomy and relationships so like categories of your of your posts, those types of things. Links is a very WP links is an old thing where people used to have like link link roles on the side of their blog, and it's still it's still there and something that you can use. And of course, your comments and your comment meta information is all stored in the database. So we can take a look at our users table. And the way to think of your table. Ama changes password later. So don't worry about
the the users table is in the way to think of a table is basically think of it like a spreadsheet. So these are every table is is kind of like a spreadsheet it has different fields. So we have our ID field, our user login, the user password, which is of course this is hashed user, nice name, the user email, those types of things, all of that stuff that you see on that user's page stored in the user's table, and you can edit all of it here or you can edit in WP admin. It's both addressed, dressing that one storage place within your database. We can take a look at our posts, and these are all of our posts, and they all have information like the post date, the content, the title, the excerpt, the posts, status, comment status, all of this information is stored in the database. So obviously we've seen some things here, haven't we? This is why hackers are interested in your database because only content they can publish to your posts. Any users that they can access. It's just icing on the cake, it is their playground. They are excited to be able to access this information because if they can access it and publish and change information in your posts, they can deface your site. They can add spam links. They can add farmer links, they can add JavaScript tags, they can add an iframe, they can completely redirect using JavaScript, they can completely redirect a site visitor who maybe is hitting that hello world page that all of our sites start with and redirect someone to a nasty part of the internet. So all of this information and all of these tables are incredibly interesting to a hacker. If they can get in here they can pollute it much in the same way as they can pollute the file system. So you're going to find all of this user information on the settings settings for plugins and your themes. That's all stored over here in WP options. Everything from your blog name to the blog description, the administrative email. You know, side note, if you ever wonder you know why you change the email of your administrator in one place and it doesn't update the admin email. It's WP options. And all of this is addressed using one language. It's not addressed using PHP. PHP just kind of wraps around the SQL language. SQL Structured Query Language is what it stands for. And structured query language is the language of databases. It tells a database insert this into this table in this format, delete this information from this table. And you anything that you can add update delete in a database is all done using SQL. And the cool thing is is you can have one SQL command do a lot of different things. So we'll talk about that a little bit too. So how do you access the database like it's great Most hosting providers have PHP myadmin. You'll find this with a lot of cPanel accounts. It's great that nexus has this as a part of their database portal. So you can just go in there and do that. If you are on a hosting provider that does not have a way to access your database beyond WordPress. Then there are some other tools that you can use. And there's one that I've used a number of times once see go to plugins here. And if you need to go find this one it is the database management tool adminer So if you just do a search when you go to add new and do a search for this, add mine some people call it add minor adminer This tool is incredibly powerful and useful. So we can basically see the entire database that we saw here with PHP myadmin. It is also here Where did it go? Oh, hello, zoom. You're hiding all of my
links. There we go.
Same type of thing. It's the same, same information, same structure. It's all of your can select data so if you wanted to, again, this was going to get into some of the SQL commands and things like that. You can export data, which when you're getting started working with a hacked site, should probably say this before I do anything else. Back up your site no matter what. You want to make sure that you have a backup so if you have Backup Buddy installed, and you've been using backup, buddy, that's already happening for you. That's backup backing up all of your files. It's also backing up your database. If you're on a hosting provider, say a client comes to you you've got a new client coming in and they have a site that isn't being backed up. Even if it hasn't been backed up. You still want to make sure that you backup you're going to preserve the evidence. This is so important before you ever try to clean up a hacked site. That is sometimes the immediate responses oh my gosh, I've got to go clean this up right now and you start cleaning it up. But definitely back everything up. Even though you're backing up malware, still incredibly important to back things up. Now let's say you don't have backup, buddy, you just came up upon the site. You've backed up the files and now you want to back up the entirety of the database. PHP myadmin. Super easy to do, you can just export this is going to export all tables from this database. And you can just do a quick SQL UPDATE. You just click go and this of course is a test site so it's going to be pretty easy to backup. So even if the database is massive, I would still recommend backing it up before you do a single thing.
And the like if you don't have PHP myadmin let's say um let's say that WP admin is shut down so you can't even get to the admin or there is an application called SQL Pro and it spelled se que UE el Pro and you can put in all of the database connection information and you can basically manage a database using this. You can also manage it via the command line. So if you had the SS H capabilities and you know the database username and password, you can also do a sequel dump by basically it basically just creates another SQL file. Same thing as what PHP myadmin is doing. There's so many ways of doing this. You just have to find a way that's going to be fastest, easiest and best and be flexible, especially if you're working on client sites. You never know what you're gonna get. So you have to have this arsenal of tools available so that you can get that information backed up. backup everything, even the spam even. Just please just always backup everything. Okay, so what happens to a database, when you get hacked, you're gonna get spam links, you're gonna get redirects you are going to get sometimes you'll get that WP options. Table polluted. Sometimes you'll get users added to the WP users table that don't that are an administrative level access and they shouldn't be there. So those are the main types of things that are going to be happening when you start seeing database pollution. So you can get all the files clean, but until you actually start cleaning up the database, your site isn't fully secure. But that being said, clean up your files first get your files all all clean and robust there because you're going to see most of the backdoors and most of the ways that hackers are going to come back in and reclaim their territory, so to speak, is going to happen through the file system. They're going to pollute that with backdoors much more than they're going to do to the database. It's just easiest and that's where you there's so many scripts out there that people share on like GitHub and everything that are backdoors. And so they'll pepper the whole site with backdoors so that they can get back in when you start discovering that a site is hacked. So concentrate on getting that file system secure, then concentrate on the database. Another thing to consider is really consider what's happening to the site is the site redirecting users to you know, an Adobe Flash download your Adobe Flash is out of date and all of a sudden you're set your computer's infected, if it's doing things like that where the sanctity of the internet and the users who are visiting your site. is at risk. Maybe it's time to shut down that site just suspended. I mean, you can do that with the htaccess file or you can even talk to your hosting provider if you're unsure how to edit the htaccess file and talk to them about suspending that site so that people aren't getting their computers infected. If something terrible is happening. Otherwise, if it's just a bunch of spam links, that's something you can just you know, secure the site, make sure that hackers can't get back in and then turn your attention towards cleaning up the site. Okay, so let's start looking for let's start looking for malware. And some things that we can look for.
There are a couple of different ways to to do this. Obviously, this is a database management tool. There are tons of different things that you can do, but the easiest thing to do, in my opinion, select all tables and then now if you'd looked at the front end of the site, and you've seen certain spam links, let's say they're all going to Viagra spam. There are things that you can do. You want to get a sense of what this hacker was up to, because they had a motivation for getting into the site. They were there for a reason they were there to you know, increase the, you know, backlinks to one of their spammy sites. And so you're going to look for some indications of compromise on the front end. Once you can see okay, this person is you know, Mr. Farmer over here and sending all of these links to to their site in order to boost their SEO. Then you can go over here to search, search all of your tables, and then look for something like you know, Viagra how many links I've seen to Viagra sites, you see some things when you clean up hack sites now this site is not infected. So here is what you want to see. Viagra is not there. Now if you saw it on the front end and the site was infected, you would see you know, which matches our do we have do we have WP posts is that all polluted? So you're going to search for the things you saw on the front end of the site, you're indications of compromise, you're going to look for them here. So this is the fastest, easiest way to do it. So you just put these parentheses or these percentage signs around the term that you're looking for other things that you might want to look for our script. So just like the beginning start of script, and you're going to want to see if you can find that anywhere in the database and
that search correctly. So not getting my zeros.
Okay, other things you can look for are base 64 decode, which is a function that they use to obfuscate things. You can also search for, you know, I would just by putting that in there like that. I would just search for a script like that. There we go. Search for script. If you don't get anything, obviously there's nothing there. And then, once you start figuring out what's going on there are some SQL queries that you can run in order to
in order to narrow down where things are. So over here,
and this is just some you're going to be able to find these types of SQL commands on the internet. They're also in that document, that bonus document checklist that we had in last session that you can download. I think that link is in the chat. And then you can do things like and this is going to help you do which is so wonderful. It's going to help you with the SQL syntax. And so select star
from and then go to your tables. It even knows what tables we're looking for. WP posts and then you're going to do where
and then the field post content like and then we can do stude JAVA script. And then you can click go and see if we find anything of course. I have a sequel error. It didn't like my JavaScript. Oh and have to do that. There we go. Still SQL error, unknown column.
Anyway, I'm trying to go by my scribbled notes here. But this is basically what you're going to do. You're going to do post content and then like and then you're going to try to find whatever it is that you saw on the front end. And you can do a search like that using the SQL language. It's basically the same thing that's happening over here, except you're getting much more granular here. You can also do a search in for your table posts and just, you know, look for the same types of things. It's the same here again, it's limiting to just WP posts. They're writing it when you're doing the search. They're writing the sequel for you. Obviously I haven't done this. We'd had it all memorized years ago. But this is what you're going to do. You're going to narrow this down and then once you have identified where the problem pattern is because you're looking for patterns that don't belong, and once you've identified where some of those patterns are, then it's time to start cleaning it up. I do not recommend just going here and going to WP posts and just doing all kinds of
cleaning commands and things like that here. What I would do if you've identified that all of the pollution in your database is in WP posts. What I would do is just go to export, just export WP posts and that will save this down as a file. And hopefully this is easy enough to see. And this is this is what's happening in the background. This is your table. And this file basically allows you to manipulate the content on the database, but you're doing it locally. Okay, so you're going to you're going to edit the SQL file using a text editor. I'm using Sublime here. And here's all of the information about your database what all of these tables all of these fields, rather in the WP posts table tells you all of the information about them if it's a text field if it's var car is just kind of a fancy way of text field, but it doesn't like reserve the same amount of space as a text field does, um date time. Obviously some of these are very self explanatory. But so what I would do when I had a polluted WP posts, I would edit it locally. So I would go looking than just using the command find and go edit all of the information out here. And then when it was time to add this table, you know and swap these tables back. Once this was clean, I would do a search for WP posts and then I would change this so that we are now creating the table WP posts clean. And then everywhere you see insert WP posts, you want to make sure that it's inserting into clean and obviously I wouldn't go through and just like type this over and over again. But you'll see this repeated Okay, so it's not going to do these just like insert everything it's going to break this down into insertion inserting into the new database into little chunks that aren't going to cause too much memory issues. So you just want to go through and make sure that all of these say clean, and we're inserting into that new table. Then once that is inserted, will you save this file, basically save it on your hard drive? And then you go back here and then you go to import? You choose that file and I'm not going to do the full thing because I'm sure that I'm going to say something. You go find that file, you open it, add it here so we've got that file and we are going to insert that just go ahead and click Go. It's going to not only create the new table, WP posts clean, but it's going to insert in all of the cleaned data into the database. Then over here, you're going to see two different fields, your student tables as a part of your database. You're going to see a table called WP posts and you're going to see another one that says WP posts clean. Then once that's done, we can swap them and basically rename rename posts the dirty posts table, rename it to WP posts, hacked and then immediately renamed WP posts clean two WP posts. So that way we're swapping out the current hacked table, swapping that into a hacked table and then replacing that immediately with WP posts clean and make that the clean content that way all of the dirty content is gone. You didn't make any error. If you did make any errors. You can obviously swap those back if you know the site. You accidentally deleted a semicolon or something ridiculous has happened. You can you know swap it back and then go back and redo it again. It's very easy to delete a table you can just click that table and then with selected you just drop that table. So once you've got that clean table as your posts table, you can drop the hash table and it's all gone. But you have a backup. So if anything weird happened, you are okay. I'm gonna stop there for a second because I see the chat number going up. I'm just going to ask if we have any questions about doing that.
So no questions about that specifically.
Okay. All right. Cool.
And the syntax for doing that is in that document. So it's just like renaming table WP posts to WP posts hacked and renamed table WP Post clean to WP posts. Very simple. Now if you're feeling brave, like really brave, you can clean content within the database. So let's say we want to go to our posts and you want to edit this you can do this here. You can edit each individual field for each individual record for each individual post. You can do that here if you want to. It's just like editing it and the WP admin. But this might go a little bit faster for you. If you don't know HTML. If you don't know how Gutenberg storing things in the database. This might get a little complicated for you but it's something that is easily done. You can also edit just using SQL. Let's say we have issue here where we have we have the entire site is polluted every single post has the same script that is going to be redirecting somebody to a bad place. To the internet. And this was an actual hack that gosh, I cleaned up so many of these. I'm going to just paste this in here and explain what it says
there are unexpected characters in here.
This worked least on the old version of my PHP myadmin. Basically though, I'll just walk through the syntax of this. So we're going to update WP posts. And we're going to set post content. And we're using a sequel term called replace and we're going to replace post content where we find this particular script for traffic trade dot life and it's basically including the script that is on a different server, including it into all of the posts and it's going to this particular UPDATE statement will go and basically just wipe that out. It's just going to replace it. This is also in the in the document that we had for last time. So you just do that here with SQL. And maybe you're going to have to I would actually instead of doing this I would actually clone the WP posts table and do the same thing I would create. There's a statement that you can use for cloning a table. So you can basically create table WP posts clean like WP posts, so then it just basically says okay, we'll just make the exact same thing. So WP posts is then cloned into a new table. And that would actually run a statement like this and the clone table that we're working with, and then swap them out and then check the site make sure everything looks okay. It's really not advantageous to anyone to work on a live site, even if it is being protected somehow so that nobody can get to it. Always good to make a copy. Whether it's on your hard drive or on another server or something like that, or even within the database here, obviously, we're not going to run we're able to really hone in on where we're running the SQL commands to just go after WP posts clean rather than going after you know we're not going to go mess up WP comments or anything like that. We can really just select that one particular table. Okay, another thing that we see a lot with, again, we're looking for patterns that the hackers have used. And typically what these hackers will do is they will try to obfuscate or hide their code and they will hide their links. So a lot of times we would see these links with inline CSS that would like do text decoration none color being white or color beings a gray that they were hoping that you would not necessarily see. And in that respect you can use but each link would be different. So each link might you know, one might be via Viagra. And another one might be you know Klonopin, or I can't even remember all of them but that so many pharma links so little time. So the link would be different. The text and that link would be different cleaning that manually was so difficult because you'd have to go through each individual post and look for those links. And so there's something called called on regular expressions that you can actually use a notepad plus plus and that is also in that document. I just wanted to mention that. Download that WP Post from PHP myadmin and then you can do a regex that looks for the pattern that looks for the color being changed. Or looks for whatever you've identified as Okay, these all look this way. Regular expressions in notepad plus plus was the easiest way to clean that type of thing up so that you're not like going to each individual link and just wiping that out and that can take a ton of time.
Another thing I wanted to mention is that the posts table does not only contain the posts that are live on your site. It contains drafts, it contains auto drafts, it contains all of the revisions that have ever been done if you're not cleaning this up regularly. And if you're starting to work with the entire SQL file for WP posts, and it's absolutely massive, make a backup of it. Always make a backup of it. But then there's a plugin called WP optimize that you can use to basically get rid of anything that isn't public. And so this is a fast way of just kind of wiping out all those auto drafts because you have somebody has been like editing this file that's been hacked 1000 times and I've cleaned sites that weren't just hacked by one hacker they got in one hacker got in polluted the site. And then the site owner didn't notice for a very long time and another hacker comes in and pollutes the whole site. And meanwhile the site owner is just updating all of their copy and changing all of these things. And there's all of these polluted auto drafts that have all of these spam links in it as well. So but but all of those aren't necessary for the for the site to operate, right. So if there is malware in drafts that don't need to you know, check with your customer at first and just say you know, there's these drafts do you need these? These are also polluted, do you are you working on these and then obviously clean those if they do need them. But if there's a bunch of auto drafts, auto saves of content that has been edited 100 times those things, their client, your clients, probably never going to notice that. They never access them that those are there so it's really easy to just, you know, use WP optimize. That is my cat deciding that he has he always interrupts live streams. It's like going at the cat scratcher right now. So this is a quick way to just like make your work easier. And that's what this is all about. I mean, you could go over every single post and clean things up. You can go over every single file and clean things up. But the whole purpose of this training is to make this as easy as possible for you. And that the key of all of this is to remember that hackers have a purpose for getting there. They're trying to make money, they're trying to increase their SEO. They're trying to increase their, you know, earnings from from all of these malicious redirects that they're doing and infecting people's computers. They're trying to make money, but they're very simplistic in what they're doing. So in order to fight back in order to save your site, it's by being able to first lock down your site with things like I theme security, backup your site so that you don't have to go through all of this kind of stuff. But then also understanding and really spending a few moments at the beginning of a hack repair to understand the patterns that this hackers left behind, because they're going to tell you exactly who they are and what that is. And you'll notice if you do a lot of these, you'll see one hacked site and you'll see the pattern there and you'll know exactly where to look and the next Tech site because all it is is pattern recognition. Purple sofa in the living room again, a I remember you and it's very easy to just get your scripts together and move that purple sofa where it needs to go in the trash. So it's it's really about pattern recognition and understanding sort of meeting your hacker and understanding who they are so that you can undo what they've done. It's very rare that I've ever run into a hacked site that wasn't didn't have some kind of pattern to it. The hard ones are those that have been hacked and react and react and the site owner just never paid attention to it and never updated anything and you know, everybody just kind of came in and left their mark. Those can be kind of complicated because it's just tedium this file has like three different hackers have added malware to the site's index file or the database has been polluted by five different hackers, those types of things. Those are the ones that can be very tedious and challenging. Which is why if you are working with customers, encouraging them to have backups, encouraging them to keep everything updated, and making sure they're using two factor authentication. All of those security tools are there to protect your customer to protect their customers, but also to protect you from having to clean up after the mess that they leave behind when all the teenagers come in and decide to drink beer in your garage, that type of thing. Because it's really that's what it's like it's you're just cleaning up after kids. So
I hope that gives you a good overview of what's going on with the database. Where you're going to find things and some strategies for cleaning those up. And And with that, I'm going to stop sharing and see if we have any questions that I can answer.
All right, we have a bunch of questions stacked up if you have asked if you have not yet asked a question, please drop it in the chat with the word question. And I'll uppercase right at the beginning. And we'll get that over to Kathy so let's get started with a question from Karen early on. Is there a way you can tell if the server has been hacked not just your WordPress install, or not just your cPanel it's the question.
Sure if if your cPanel has been set up correctly, and there's other hacked sites on your server, that is really your hosting providers in their best interests to find and clean up because those those hackers are using resources that belong to someone else. That's really what it boils down to. There's no way for you to really know unless one you know you're on a shared IP address and you know, your stuff is all being marked as spam for some reason. And then you find out oh well, you're on the same shared IP address with a hacked site that sending spam and that's now affecting your reputation because of that shared IP address. So that's one way you can tell other than that, it's really up to your hosting provider. And there shouldn't be cross pollination of malware across C panels if they're set up correctly. It should stay within that one cPanel that one PHP user on that particular site or group of sites, depending on how it's set up.
Very good question from Kenneth. Kathy, are there some types of customers that are hacked more often than others?
Types of customers that get hacked more.
There's so many different levels of security. It really, honestly, if you're working with customers security is your differentiator, because not only are you making sure that that site is secure, you're teaching your customers the ability to have good security decisions in all of their business. You're helping them protect their bank accounts, you're helping them protect their Facebook account you helping them protect the entirety of their digital life, but it starts with WordPress security. It starts with understanding what hackers are up to and what they're doing at WordPress and that kind of knowledge extrapolates out into our digital lives. So we as people who are you know, influencers with our customers, we have not only a responsibility to uplift their security understanding, we have a privilege in doing so when you do that you are differentiated against all of those other web developers who are just like okay, I'll clean it up for you, whatever, but educate your customers and help them understand how important security is for everything, not just WordPress.
Absolutely. Dave has a question here. Shouldn't you also take the site offline when you're cleaning it up? For example, if it's only a patchy, I can use the htaccess to limit you talked a little bit about that?
Yeah, it depends. You know, it depends on what's happening with the site if it's redirecting to the bad place to the internet, take it down, you know, you don't want or if there's, if you get a WooCommerce site that's got a card skimmer JavaScript thing in it, please take it down for the safety of everybody. Take the site down. If it's just spam links in the database and you know, there's, you know, Viagra links in the post, who's that hurting really, it's obviously hurting the site and the site's reputation, but it's not causing greater damage. So in that respect it I wouldn't take it down. I just leave it up and try to clean it and just swap it so that that malware is just gone all at once. But if it's hurting people, or if it's doing damage to the brand ticket down. Yeah, good.
And so along that same line, Ben Anderson was asking, is there anything you think about when it coming and when it comes to taking a site offline like that, in regard to SEO and Google scanning the site? How long can you have it offline like that without risking SEO and other issues like that?
Um, tip,
I mean, typically, it would at the worst. Well, there are a couple of big ones that were down for a couple of days, but at the worst I would could get a hack site done in three to six hours, depending three hours. I've cleaned tax sites in an hour. But you should be able to get it done in six hours without Google really noticing.
Yeah, a lot of times, you know, especially if you're running Google Search Console on your site. Google's the first one to tell you that the site, you know, and so it's I I'm not an SEO expert, but I would imagine it's better to have the site down a bit and then clean then have it up and hacked.
Sure. Yeah. I mean, these are all things to pull, put into balance. If you know if you're going to take the site down, but the site is making $100,000 a day and taking it and you only have spam links on there. Do you want to take it down and lose $100,000? Or if it's, you know, get a credit card skimmer on there, and there's all kinds of other problems. But I mean, ideally, you want the red screen of death. Google's malware warning to go away. We talked a little bit about how to do that last time, but you just want to get the site up and functional as best as possible. Typically, I would not take a site down unless there was something like I don't want anybody else to have this terrible thing happened to them. But a lot of times Google's already done it.
Yeah. Okay. Paul would like to know if there's a list of words or phrases that should be searched for, like script JavaScript iframe.
Um, yeah, I would. All of those things. I'll just list them I should have put it in that document. But I didn't sorry. I would look for iframe base 64, decode eval. So E V, Al, script. Other things, you're going to know. You're going to know what the indication of the compromises from you know, who told you that it was hacked, right? But obviously, there's other things that you want to look for within WP options. But if they have added a bunch of things into WP options, that's when I would really turn to a professional who does this all the time, because that's where you've got serialized data and you can break things pretty easily. Or just use the WP options, clean thing and create a new table and then swap those things. But options table is one place where I kind of advise people to, you know, talk to a professional who does this all the time so they know exactly how to clean that kind of stuff up. But most of the time, you're gonna see stuff in posts, you're gonna see stuff in pages.
Yeah. And you know, as soon as you find something that's been hacked, and there's another phrase that you haven't searched for yet, search for that one, right, right, right, looking for those patterns.
Right and you know, hackers are always I can't tell you like, okay, the one domain name. The trait was a traffic, traffic trade. I went yesterday and I started looking at all the different domain names of like indications of compromised that have been like within the past year. There's so many of them and what ends up happening is they put malware on a site, Google recognize it, and they've got to move malware to another domain name. They're going to move malware to another hacked site. They're going to do all of these types of things. So you always have to be a just be looking for something that's not normal. That doesn't look like it fits. You're looking for the sofa in the living room that's never been there before you're looking for the thing that's out of place, because it's going to change all the time.
Yeah. John would like to know if you only had command line access and not a database manager tool, how would you clean a database?
Well, I've learned to MySQL before PHP myadmin existed, and I was like this. You can do all sorts of the SQL statements. You can do them on the command line. When you do the MySQL dump that's on the command line. So you can actually addressed the database you can log into MySQL from SSH, and you can put all of those same same types of things. You can do a select star from WP posts. Pull that into a file that you can then download if you get FTP or whatever methods of downloading you want to use. You can download it that way you can create, you know all any, any SQL statement that you can run through PHP. MyAdmin is something that you can run on the command line once you're logged into MySQL
doesn't sound like any fun at all. It's not
my case me my admin, it's such a gift.
Yeah. Okay, question from Karen. What about all those text edits to the WP Post cleaned all those stay named that way when you rename the table back to WP Post, and other I think how do you clean things there and then rename it and those changes?
Stick? Yeah, they stick because what you end up doing you you go into that SQL file the dot SQL file with a text editor like Sublime, and then any edits that you make and then you save it. And then you go to PHP myadmin and you import that file, and it's creating that database, that database table. And it's inserting all of that content into that new database table. Any edits you make to that file goes with it.
Yep, all right. Let's see, Elaine would like to know, questions about editing malicious urls in the database without bringing down the website. Can I directly edit URLs in the database?
Yes, you can. Highly recommend that you clone the table and work on a separate table and you're going to be fine. Now that you're going to focus primarily on that, that WP posts table and the post content field within that table. So the Think of WP posts like a spreadsheet. So you've got a spreadsheet of all of that data. And one of those columns is post content and you can even go into that particular that line on your table and go into that record and edit post content in there. And then just you know, click Save and it will automatically edited on your on the server. I really recommend doing it on a cloned table. Yeah.
Ben would like to know is it a good idea to go after a site that a hack points to or at least tell them it's not good marketing
you know, you can go down a lot of rabbit holes go down a lot of rabbit holes.
It's very interesting sometimes finding out you know, you'll you'll kind of get to know some of the players and hacking of WordPress. It's like Oh, this guy again. They move all the time. Now if you you know, if you have a script being added to your site from Sally's cat blog, and Sally's just Sally with her cat blog, and there's a malicious script on her site. Maybe you might want to find out a way to contact Sally and let her know maybe she just doesn't know any better. If you're feeling altruistic. That might be a good thing to do. But typically they set up a domain name, they put their malware on that domain name, that domain name gets blacklisted by Google, they move on to another domain name so it's not really worth a lot of your time to really like chase after that. It's like It's like dealing with the dingdong ditch or kids in the neighborhood. Trying to find moms to stop them from doing it or do you just like kids are going to packers are gonna hack and kids are going to be kids in the summertime right?
Yeah, you know it's it's like the spam texts and spam calls you get the from they're just they're fake numbers or burner numbers or something and you'll never track him down. Andreas would like to know what tools that you might recommend to scan the website files for malware files.
There well.
Recognizing malware is one thing, malware is just like those domain names are changing all the time. There are files that are changing all the time. There are some patterns though, that you can recognize. I think security is really great. Because it's going to tell you something's changed. something's changed. You know once you go set up i theme security, any kind of changes it will alert you that there's there's been a file change if you set that up. So I would do that because when you start seeing that and then you can go see you know, what changed? Immediately backup everything. And look at your log files and see what's going on. So I think security is a great way to just kind of stay on top of things. Kind of just understanding who's who's coming into the apartment building, so to speak.
Yeah. All right. All right. I've got a few more questions here. And we're gonna stop with these questions. That I already have listed here. Shaquille LEA would like to know when a hacker makes a backdoor in the file system, does the backdoor allow them to access the database or the Will there be a separate backdoors for the database.
It allows them access to everything so backdoors there's one file called WsL shell, and it's very popular for hackers. And it basically gives you the file system of anything that that user that is running PHP can see. So that includes your includes your WP config file, it allows them to edit files allows them to upload files, so they've if they've gotten access to your file system, they've got access to everything. And maybe they upload a different file that also has, you know, specific things like a PHP myadmin or an admin or type of script that allows them access.
Alright, another question. From Scalea. How do you know that a database is secure? Is that a responsibility? That's up to the web host?
Good question. Um, again, there's a lot of gems. We talked about a lot of different ways. There's a lot of doors into the into the mansion. of MySQL. There's PHP myadmin. There is the PHP in your WordPress site. There is the command line there is FTP to the file system that can also then access the database. There are many different ways that someone can connect to a database. It is good to make sure that you are with a secure host like Nexus and liquidweb to make sure that your database is secure. I have seen hosting providers long time ago that were using insecure versions of PHP myadmin. That allowed that was the Justin Bieber site to get into the database without ever touching the file system. So obviously, it's a good question to ask your host and make sure you're on secure hosting always.
For sure. Alright, last question of the day it goes from Derek Derek says I see many sites that redirect to Chinese. Bing sites are similar. What do you make of this? Do they hack the htaccess file?
Hmm, yeah, there used to be a Japanese hack. That was the htaccess file. So what would end up happening there is the htaccess file would actually check the referrer where's this? Where's this user coming from? Did they just come from Bing? Or did they come from Google? Well then redirect them to this other place or show them these other pages and they'd have these sitemaps and, and the actual hacked site, they'd have a sitemap that would and they were very successful in boosting, boosting their visibility in the search engine result pages by doing so. But it was in you would visit your site and it's like everything looks fine. Why is Google showing all these Japanese characters in my site content? This is so weird, and it was all done through through HT access. A lot of times they would also put the google verification site, the webmaster verification file on that server. So they would take over the Google Search Console and then they would update update sitemaps and all kinds of stuff there. So you know if you're seeing that you've got to go look at search console, make sure that secured as well. We talked about that. I think last week, or last time as well. But yeah, that's mostly you're gonna see that in the htaccess file, but there also might be Sitemaps on there. You might not see any email or anywhere else on the site, other than maybe a backdoor or something but it'll be in the sitemap it'll be in the Search Console. It'll be an HT access.
Very interesting. Great stuff. Kathy, we're finished with our questions. Really interesting dive into the WordPress database and the ways that a site can be exploited Any final thoughts as we're wrapping up today?
Um, that's it. You know, I did want to mention one thing about that there's an article in WP tavern about Web Fonts being loaded. Um, they want theme developers to add web fonts to all of them so that because of GDPR concerns, and I just wanted to mention Kadence because Kadence is really awesome. And if Kadence even the free version allows you to store your your web fonts locally. So if you're one of our Kadence aficionados, you already have the sav just go into your customizer general performance and you can automatically host your fonts locally. That's been in the news today, and I just wanted to mention that because I know we have a lot of Kadence people here. Yeah, for
sure. All right. Thanks everybody for being with us today. I want to mention that we do have a special out of this webinar special deal if you are not yet and I theme security or a Backup Buddy customer. There's a 35% off deal for I think security and Backup Buddy plans or our Essentials Bundle 35% off with a coupon code DB secure, like database secure DB secure 35% off of all I think security Backup Buddy plans or our Essentials Bundle. Well, that's kind of wrap it up for us today. Thanks again for your expertise today. Kathy, appreciate all of you being with us as well. Hopefully you've learned a few things just like I have again, my name is Nathan Ingram. I'm from everybody here and I think I hope you have a great rest of the day. We'll see you back here tomorrow and I think is training where we go further together.