How did my DB get hacked?

Databases
,
#1

I received an email from a hacker stating that they obtained the databases for a site I manage and are demanding bitcoin. It seems like they actually did get the DB data because they provided a sample of the data and it does appear to match up.

The website uses PHP (an older version) and MySQL. The code is a bit old for this site, but, I’m not really sure how they were able to get the database contents via the website, which appears is how they did it. I don’t believe there was a breach of the actual server. I got a notification of a signup to this website a few weeks ago that seemed suspicious and when I looked up the IP number for that signup in the server logs I see lots of entries like this:

[Sat Apr 23 11:19:33.527742 2022] [cgi:error] [pid 9898] [client xx.xx.xx.xx a:45818] script not found or unable to stat: /var/www/cgi-bin/htimage.exe
[Sat Apr 23 11:19:33.556184 2022] [cgi:error] [pid 11858] [client xx.xx.xx.xx a:45700] script not found or unable to stat: /var/www/cgi-bin/htmlscript
[Sat Apr 23 11:19:33.559824 2022] [cgi:error] [pid 11784] [client xx.xx.xx.xx a:44400] script not found or unable to stat: /var/www/cgi-bin/imagemap.exe
[Sat Apr 23 11:19:33.571326 2022] [cgi:error] [pid 9934] [client xx.xx.xx.xx a:45712] script not found or unable to stat: /var/www/cgi-bin/index.html
[Sat Apr 23 11:19:33.597727 2022] [cgi:error] [pid 10490] [client xx.xx.xx.xx a:46344] script not found or unable to stat: /var/www/cgi-bin/login
[Sat Apr 23 11:19:33.689271 2022] [cgi:error] [pid 3341] [client xx.xx.xx.xx a:45634] script not found or unable to stat: /var/www/cgi-bin/login.cgi
[Sat Apr 23 11:19:33.858026 2022] [:error] [pid 10644] [client xx.xx.xx.xx a:46414] script '/var/www/cgi-bin/login.php' not found or unable to stat
[Sat Apr 23 11:19:33.858506 2022] [cgi:error] [pid 11691] [client xx.xx.xx.xx a:45822] script not found or unable to stat: /var/www/cgi-bin/printenv.pl
[Sat Apr 23 11:19:33.865787 2022] [cgi:error] [pid 9310] [client xx.xx.xx.xx a:45476] script not found or unable to stat: /var/www/cgi-bin/test-cgi
[Sat Apr 23 11:19:33.866803 2022] [cgi:error] [pid 9898] [client xx.xx.xx.xx a:45818] script not found or unable to stat: /var/www/cgi-bin/php.ini
[Sat Apr 23 11:19:33.868400 2022] [cgi:error] [pid 10529] [client xx.xx.xx.xx a:45442] script not found or unable to stat: /var/www/cgi-bin/test.cgi

I removed the actual IP just for security purposes (it appears to be an Amazon data center IP when I look that IP up). There are thousands of lines like above probing all kinds of directories and files on the webs server, so, I think they must have found some vulnerability in a PHP script that let them somehow obtain the databases.

If anyone can tell me how this would be possible, that would help me find the vulnerability so I can try to fix it. Right now, I have no idea how they did this via a web browser. Are there any tests I can do to check this kind of vulnerability and see how they did it?

Let me know if you need any other info, thanks in advance for any advice.

The value of named place-holders in prepared statements
#2

Sql injection was probably used with an existing SELECT query to cause the contents of any available database table to be output onto the web page.

You would need to post actual code to get any specific help with it.

#3

I’m not sure what code to post, as I don’t know which code has the vulnerability.

How would they be able to run a SELECT command for MySQL from the URL? I’d like to try this myself to see if it works so I can try to pinpoint where they did this.

Is there any way to prevent SQL injections? The code is using older commands such as myql_query(). Some of the code that has been upgraded in the past year is using the new PDO query stuff. If there’s a way I can try an actual injection command on my site to see how it reacts, that might help me figure out where the vulnerability is.

#4

It would be naive to assume it was done through a web browser. There are numerous ways to hack a server or an application. Without someone doing an actual pen test you cant be sure where all the vulnerabilities are. Having outdated versions of software definitely does not help.

It could be as simple as the same credentials were used on some other site that was hacked and they simply just logged in. There are just too many possibilities without seeing the code or the server.

#5

How much total code is there? I only listed one possibility. Other possibilities, especially with older code, are things like - including remote php code, file uploads allowing a php file to be placed onto the server then requested, register_globals being on which allows any php variables to be set to bypass/elevate user privileges, cross site scripting via javascript to allow any buttons an administrator can push to be pushed when viewing things like user posts, profile information, …

Sql injection involves including sql query syntax in an external data value that you put into an sql query statement, that satisfies (escapes from) your sql query statement and adds its own sql query syntax, usually a UNION SELECT … query to get any data it wants from any table(s).

Use a (true) prepared query when supplying external, unknown, dynamic values to a query when it gets executed. Note: PDO has emulated prepared queries, that should be turned off, that if you haven’t set the character set, when you make the connection, to match your database table(s), is still open to sql injection.

1 Like
#6

As stated, there are many attack vectors these days.

Maybe a good place to start would be looking at the database calls, with attention to how variables are put into queries.

#7

I have taken a look. There are numerous vulnerabilities including XSS and Blind SQL Injection vectors. I wont dare say what Php version it is running. Waiting on code for review.

#8

There could be numerous things that could attribute to this outcome. Since you using a login system, we can break that down first. So what I tend to see from a lot of folks on here and everywhere when they write a PHP login system is that they don’t really care about security. Not talking about security in the sense of SQL injections and what not, but something like brute force. If an attacker knows there’s an entry point, they will try every single possible way to get down to your database.

This means if you’re outputting database errors to the screen, well, that’s the culprit. Other things like providing a very weak hashed password could also be the problem. Using things like MD5 and SHA1 to hash passwords are forbidden in a login system and this is why. Especially if the passwords are just stored as plain text. Other things like not enforcing stronger passwords or even the use of passphrases could be it as well. Lastly, there is no other top level security layer such as multi-factor authentication put in place. This would have deterred the attacker. The attacker does not need to know your database username and password. If there is a weak point in your script and they see an error being outputted to the screen such as directory paths or what not, they can easily get into your ACTUAL filesystem on your website and have gotten the database information that way.

There could be a ton of other possibilities as to how it got to this point. Ultimately, I hope this is a good lesson to start using updated code.

1 Like
#9

Sorry for dragging this off-topic, but this is something I’ve wondered about. I can see how inserting additional terms into the query might allow an unscrupulous user to delete or drop a table, but how would they be able to display anything? How would me (well, not me) adding an extra SELECT clause to an existing query alter what the code for the page displays?

As I type this I wonder whether you’re referring to something like a SELECT CONCAT (xx, yy) to stick other values into the one that actually does get displayed.

(No, I don’t have any live sites based on PHP that require any kind of security).

#10

^ This is used as part of what I have seen.

If you have a main SELECT x, … query that you are going to loop over to display the result of, any row(s) that an injected UNION SELECT query adds to the result set will also get looped over and displayed. By using CONCAT, for the columns you want, in the first SELECTed position, the concatenated column result will get displayed the same as the x column values from the main query.

1 Like
#11

It depends on many variables, including how the code display the data pulled from the database.

In most cases, you will not be able to see additional data by SQL injection, but you can for example get access to information tied to other accounts on the system or update other accounts on the system. Basically, using the features the website has to display/update the data, only that you apply the change to other accounts than the one you are logged in with.

In the cases where someone gets access to the content of the database, it is normally due to being able to inject PHP code into your files that allow them to do anything the PHP/Webserver user is allowed to do on the server. Or by breaching the server itself, and getting access that way.

2 Likes
#12

I recommend digging into and learning from the OWASP Top 10. The list is updated every few years to cover the biggest threats to network connected applications. Attackers generally don’t use real web browsers. They use automated tools like nmap to find vulnerable networks and applications. Everyone’s web server logs contain all kinds of random incoming attack attempts, usually for targeted applications but sometimes site-specific. There is no good way to prevent such requests in the first place as that is a never-ending battle. You just have to write your applications defensively from the very beginning such that you assume your application will be attacked from multiple vectors.

Determining how your database was exfiltrated and closing the holes is just the starting point of the effort you need to undergo. You also need to evaluate the severity of the breach. For example, if you store credit card info, a huge no-no these days but some people still do it, you need to notify everyone who is affected so their bank can issue updated cards. Failure to report certain data breaches to the authorities in your country of origin can make you be held liable for financial damages. Once you’ve gathered details and closed the holes, I recommend contacting a lawyer that specializes in data breach mitigation to determine what you are required to report by law.

Outdated PHP is not necessarily a problem. For example, most Linux package managers select a specific version of PHP for their distro and then backport security fixes from later versions of PHP as bugs are patched. As long as packages are kept up to date on the system, then PHP is guaranteed to be running with the latest security fixes and, as a result, there is no issue. The only exception to that rule is if the OS itself is out of support. If you are running a custom-built PHP, then keeping it up to date is your responsibility. So just because PHP reports an older version does not necessarily mean it is a problem. The same is true for the web and database servers. A good approach is to rely on the system package manager and enable automatic updates of packages to run daily. That way the system is only lagging behind 24 to 48 hours from any particular global update. That is generally better than anyone can do manually because system package maintainers usually get access to security patches before they are made public in official source code repositories. Updated packages are, as a result, sometimes available ahead of the actual announcement of the vulnerability and source code release.

My recommendations are to never pay attackers and don’t ever respond to them - that way they don’t know whether or not you received their email. These days, there is no guarantee that any given email will be received thanks to spammers/scammers/email marketers. Also, you can’t guarantee that they won’t just take your money and release/resell the information anyway, so it is best to just ignore the attackers’ demands. Close the breaches and secure your infrastructure to keep it from happening again. And then follow these two rules of thumb for the future: First, assume everyone is an attacker and therefore write your code and manage your systems defensively. Second, convenience is the opposite of security. If something is inconvenient for you, it will also be inconvenient for an attacker.

2 Likes
#13

I really appreciate all of your replies (and patience) to my post. @benanamen has been helping me via DMs here for the past 24 hours, I am so incredibly grateful to him. I sent him my code and DB and he’s been pointing out all the vulnerabilities that need to be fixed. I know I am using an ancient version of PHP, and that’s something I will address eventually, but for now I think I need to try to plug the biggest holes in what I have and then work on completely re-doing the mess of old code and upgrading PHP and MySQL to the newest versions.

I am using CentOS 7 on the server, and I use yum to keep all the software updated, so, even though I’m using old versions of PHP and MySQL (actually MariaDB), I am keeping the server updated on a regular basis.

One thing I did is change the privileges of the user for database calls on public websites to only allow SELECT, as the websites really don’t accept any input or use any tracking that needs to be written to a database. Will that help lessen the chance of someone being able to screw with the data via SQL injection?

I also need to address the password storage situation. It’s not plain text, but, is an older hashing technique that isn’t very secure I’ve learned.

Thank you for all the advice. I have to admit I’m completely overwhelmed, but, I’m working as fast as I can to make these sites more secure. You’ll be seeing me here quite often in the coming months!

1 Like
#14

Passwords aren’t being stored plain text, but I was using an older, not very secure method of hashing them. What is the best way to store passwords in the database, and then decode them for logins?

#15

The code is still using the old mysql_* queries for most of the site, but there are some parts of the site that I’ve started upgrading that is using PDO. Here are the settings for PDO:

PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,

So it seems like I am not using emulated prepares, at least, for the parts of the site that are using PDO.

I am using prepared statements, but only partially. Sometimes I’m injecting $variables into the SQL statements, and @benanamen pointed out that I should not be doing that. Is that true even if I’m only doing a SELECT that is not based on any external data input by the site visitor? The public-facing website really only does Selects, and I have limited the MySQL user privileges for the website to only allow SELECT when accessing the database for now. But for the Admin interface where content is entered and uploaded, I now know that I need to modify some of the code to use only prepared statements for ALL data input by the user. Right now I’m only doing that for some data, not all.

#16

The error mode should be set to use exceptions. The connection already always uses exceptions for errors and by using exceptions for all the rest of the database statements that can fail - query, prepare, and execute, you will get ‘free’ error handling for those statements without adding code at each statement, and letting you remove any existing discrete error handling logic you may have now, simplifying the code. The only time having error handling for database statements, which would use exception try/catch logic now, are for errors that the user on the site can recover from, such as inserting/updating duplicate or out of range values. In all other cases, there’s nothing the user can do about the type of errors that would be occurring, so there’s no point in your application code trying to specifically handle them. Just let php catch the exception in these cases, where php will use its error related settings to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed/logged the same as php errors.)

Yes. A prepared query prevents any sql special characters in a value from breaking the sql query syntax, for all data types. A prepared query also simplifies the sql query syntax, since all the extra quotes, concatenation dots, and {} that are used to get php variables into the sql query are removed, when you remove the variables and replace them with simple ? place-holders. A second point of a papered query is that it provides a minor performance improvement (~5%) for queries that get executed more than once, with different input values, since the sql query statement is only sent to the database server once, where it is parsed and its execution is planed only once on the database server.

If you read the examples I have given, it is a SELECT query that’s used to grab the data from any table. The key is to prevent any sql special characters in a data value from breaking the sql query syntax.

A point not made yet, is that for at least MySql/MariaDB database, they decode a hex encoded string, when encountered in a string context in the sql query statement, back to the original encoded string. This is how a lot of sql injection is accomplished, since it allows the value to bypass any checks and ‘sanitation’ attempts made to it. So, it is imperative that ALL external, unknown (past, present, and future), and dynamic values be supplied to a query when it gets executed by using a prepared query.

So, using the PDO extension, always using a prepared query with external, unknown, dynamic values, using implicit binding by supplying a simple array of input values to the ->execute([…]) call, using simple ? place-holders, and using exceptions for database statement errors makes your database code safe for all data types and results in the simplest code and simplest query syntax.

3 Likes
#17

I believe the best way is to use password_hash() to store them, and password_verify() to check them. If you have a query anything like

select username, password from users where username= ? and password= ?

you’ll have to drop the check on password because you can’t do it that way - password_hash() will return a different hash every time it’s called on the same password. So you’d do something like

select username, password from users where username= ?
/ retrieve the values into $row
/ user-supplied password is in $password
if password_verify($password, $row['password']) {

In this case the password_verify() handles comparing the user-supplied password from the login form to the hashed one stored in the database.

Or am I out of touch and there’s something better again now?

3 Likes
#18

Nope, this is still the recommended method :+1:

1 Like
#19

As @mabismad points out, they can still get data from your tables using only SELECT queries, so prepared statements are still a necessity. But I do believe this is still a wise move to limit a DB user to the absolute minimum of privileges they require. If a hacker does get control of a DB user account, you limit the damage they can do.

The only exception, where you would not use a prepared statement is in a complete hard coded query. That is one that will always be exactly the same on every call, with no variables at all.

Not just the user, but data from anywhere, trust nothing that goes in as a variable.
Also be aware of what can be(come) user input. Things like “hard”-coded values in hidden form fields. These are so easy to edit on the client side. Anything from $_GET, from $_POST, anything that is not explicitly typed as part of the query string.

I believe it remains current by using the latest hashing methods available in the PHP version you have.

I think in a lot of cases, it’s not that they don’t care, it is just naivety, they don’t know the threats, they don’t understand that their work is insecure.
Though it does bug me when I see it time and time again, an inexperienced coder posts their script, and the good people of the forum point out the glaring vulnerabilities in it, and they just ignore the advice, happy just to get it “working”. Probably they think it’s too much hard work or too difficult for them to understand, so sweep the idea under the carpet thinking it won’t happen to them.
But I don’t know of any site I have been involved with that hasn’t been attacked, gladly none have been breached. But all get numerous attempts upon them, and I don’t think I’m any different in that. Bots are out there 24/7/360 scouring the web looking for weaknesses. If you have systems in place to spot them, you will be staggered at how common it is.
The advice is sadly a little late for the OP, but for anyone else reading this, the key is to start taking security seriously before you get hacked.

3 Likes
#21

Is there a way to make mysqli_* queries as secure from SQL injection as PDO? I ask because I’m working with really old procedural PHP code that uses tons of mysql_* queries. I’m finding it really time-consuming and frustrating to replace it all with PDO. It would be a lot easier to replace it with mysqli queries, but, if that will still leave the database open for SQL injection attacks, then I’ll just grin and bear it. Also, I don’t need to be able to support all kinds of databases, this code isn’t portable and will be used in a controlled environment.