Does prepared statement protect me from seeing garbage in my database?

Question

Basically I started to use prepared statements after I've read some articles on the internet which said that it's an easy way to prevent sql injection. Jumping to my scenario, I have a logExit function which inserts into the DB the page that the visitor came from on my website and the page where he is going after leaving my website.

public function logExit($url){
    $HTTP_REFERER = ($_SERVER['HTTP_REFERER'] ? $_SERVER['HTTP_REFERER'] : '');    
    $REMOTE_ADDR = ($_SERVER['REMOTE_ADDR'] ? $_SERVER['REMOTE_ADDR'] : '');

    $params = array(
        $REMOTE_ADDR,
        $HTTP_REFERER,
        $url
    );

    // custom query function ~ prepare/execute
    $this->query("
        INSERT INTO exits(ip, url_in, url_out) VALUES (?, ?, ?)
    ", "sss", $params);
}

The thing is that, today when I logged into the admin panel to check how many visitors did I had today and where did they left from my website, I got tons of alerts. When I checked my database I had like 13500 exits from the same IP to different pages but the issue came from the url_in field, here are some examples:

     ip    |               url_in                |    url_out    |
-----------+-------------------------------------+---------------|
  xx.xx.xx |'"></a><script>alert(11779)</script> | xxxxxxxxxxxxx |
-----------+-------------------------------------+---------------|
  xx.xx.xx |            ,alert(11774),           | xxxxxxxxxxxxx |
-----------|-------------------------------------|---------------|
  xx.xx.xx |                %c0%a7               | xxxxxxxxxxxxx |
-----------|-------------------------------------|---------------|
  xx.xx.xx |                 %2527               | xxxxxxxxxxxxx |
-----------+-------------------------------------+---------------|

HTTP_REFERER = url_in

$url = url_out

So this seem to be strange because as far as I know the HTTP_REFERER should be an URL. How did this mess get into my database and how to prevent it?

Thanks

PS. The hosting company showed me this log, what does it mean?

access-logs/my-example-website.com:xxx.xxx.xxx.xx - - [21/Dec/2017:18:59:59 +0200] "GET /mode.php?admin_mode=1&referer=javascript:alert(16555) HTTP/1.1" 404 - "-" "Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko"

Answer 1

This is working as designed, you're protected from SQL injection problems. The key thing here is SQL injection attacks are just one vector of many.

In order to safely use that content you must escape it properly for the context in which it's used. If you're displaying that as HTML that means using a function like htmlspecialchars to prevent XSS attacks, or if you're encoding that in JSON or JavaScript then using json_encode to properly encode it for that context.

If you're seeing junk like that in your database it's probably because you're being probed by some automated attacker. Most production sites will see tens of thousands of attack attempts per day, and sadly this is just how things are. If you'd like to quiet things down you can use things like fail2ban to ban repeat offenders.

Answer 2

That you see those results in your table, and not a series of errors in your error log complaining about malformed SQL shows that the prepared statement + bound variables is doing its job.

Please note that it's not the prepared statement that protects you from SQL injection, but the use of bound variables rather than building SQL statements from dirty data.

Answer 3

Prepare statement is the best way to safe you from sql injections (you should use a wide used framework but i guess this is the case here)

Prepared statements will not prevent you from getting garbage data into the database. Even attack vectors which could be succeed later when interprated f.e. as html (xss) could get into the database. But in this case the root cause is a missing validation/escaping before rendering and not the database framework.

However to archive clean data in the database you have to validate the data before you insert them (input validation). And let me tell you there is no easy way to accomplish this for all data because it depends on the nature of the information you have in your particular application. And there is no silver bullet for this.

Answer 4

Others are right. You have successfully avoided SQL injection.

But cybercreeps are sending you maliciously crafted referer strings.

You know the HTTP "referer" header is supposed to contain a URL. The remote address string is supposed to contain an ip address or hostname, with a port number attached. (not a URL).

So, before you insert referer into your database you can check it to see whether it's a well formed URL or something malicious. If it's malicious you can just blank it, or change it to http://malicious.example.com, or do whatever you want to get rid of the rubbish, before you INSERT it.

In your case, use php's nice built in filtering capability. Add these lines right after you set $HTTP_REFERER for the first time.

 $HTTP_REFERER  = filter_var( $HTTP_REFERER, FILTER_VALIDATE_URL, FILTER_NULL_ON_FAILURE );
 if ( is_null( $HTTP_REFERER ) ) {
    $HTTP_REFERER = 'http://bogus-referer.example.com/';
 }

or something.

Do something similar for remote_addr, using FILTER_VALIDATE_IP.

And remember this: you're not paranoid. Clever and motivated people are actually plotting against you, and trying to pwn your web site.