I have a website with all my data stored in a mysql database. A few weeks ago I added a search feature. Everything was going fine, until a few days ago. My website become extremely slow and unresponsive. After troubleshooting with my hosting company, as well as cloudflare (anti-ddos company), we figured out the problem was with the search script. Once I disabled the search feature, the load on the server went down significantly.
I created this search feature all by myself, and I think something may not be written as good as it should. The script works exactly as I want it to work, it’s just that there seems to be room for exploitation by nefarious individuals which results in the server load going very high thus slowing down the website.
Some background: this is a high traffic site, and it resides on it’s own managed dedicated server, with lots of cores and RAM. Normally the site loads very fast, but after the search feature becomes exploited, the website load sky rockets and the site becomes very slow.
So, I would very much appreciate if someone could take a look at my code, and suggest any edits to make it safer.
My code is as follows, and resides on 3 pages. First one is the search form:
$searchterm = $_POST["find"];
$query = "SELECT * FROM websiteDB WHERE keywords LIKE '%$searchterm%' OR title LIKE '%$searchterm%' OR description LIKE '%$searchterm%' OR url LIKE '%$searchterm%' ORDER BY time DESC";
$result = mysql_query($query) or die("Error: " . mysql_error());
That script is very vulnerable to SQL injection, it must change.
Mysql has now been removed from php, so should not still be used.
Moving to mysqli or PDO will not just keep you current, but enable you to use “prepared statements” for queries which can help protect your database.
You should also sanitize any user input.
I don’t get the three scripts.
You first use post, then in the second pass the post data to a url variable.
Then in the third, you use the post data again. I don’t see the logic in that.
Yes, the logic of having the search.php file does seem wasteful.
form sends to search.php
search.php sends to find.php
find.php queries database
when it could be
form sends to find.php
In addition to what SamA74 posted about the PHP needing a rewrite, depending on the size of the websiteDB table I think doing open-ended wildcard full text searching on 4 fields is prone to eventually becoming a real pain point if it isn’t already.
The exploitation issue is a PHP code issue, not a Database issue.
So, focusing on the poor query
SELECT * FROM websiteDB
WHERE keywords LIKE '%$searchterm%'
OR title LIKE '%$searchterm%'
OR description LIKE '%$searchterm%'
OR url LIKE '%$searchterm%'
ORDER BY time DESC
Do you really need every field (the *) or only certain known fields?
Are the WHERE - OR fields in the order in which they are most likely to match first?
You can use this info to identify bot agents and IPs to set up blocks in htaccess or elesewhere to rebound a number of them.
Creating blocks has greatly reduced the number of attempted SQL attacks on a site I manage, where I monitor and record such activity.
Also, searching large database tables like this can take long. If that is the case then consider caching the results for a period of time.