Security strategies in PHP website: SQL Injection, XSS attack and 2nd order SQL Injection

Question

I am a tyro in web security and have been researching on it for two days. According to OWSAP, SQL Injection and XSS attacks are the most common over the internet and at the minimal must be handled by every programmer.

So whatever I understood to protect them is the following (you are requested to correct it or add if I am wrong):

Use PDO and prepared statements to prevent SQL Injection

PDO and prepared statements are sufficient to prevent (first-order) SQL Injection and we do not need to do any escaping on input data as the driver handles that.

BUT this may lead you prone to second order SQL injection (see this for more) where a data like ' OR '1'=' may get stored into the database after passing through the PDO and prepared statements as they store raw data and to prevent this makes me feel to rather escape the string first and hence

use $pdo->quote($string) before passing it to prepared statement for storage

But since I also want protection against XSS attack I should use htmlentities() as well (or htmlspecialchars() for minimal case) .I should do this at the output but I may prefer to use at the input side if my output is targeted for HTML only

To summarize,my steps would be

$string ='raw input from user';
$escaped_string=$pdo->quote(htmlentities($string));
$pdo->execute('query to store $escaped_string  into the database');

while ouputting

simply echo the stored field from the database.

I want to know whether my approach is secure or not?

Answer 1

If your code is open to second-order attacks, you're not using prepared queries correctly, and do not fundamentally understand what you are doing.

The point of escaping data in a query is to disambiguate the data from the command. The point of using parameters in queries is to fundamentally separate the data from the command. Both of these have absolutely nothing to do with how data is stored in the database.

Every query you do should use parameters for arbitrary data being used within them. If you do not do this, you might as well have no protection at all and will undoubtedly have errors in your application. Always use parameterized queries (and actually use those parameters) for arbitrary data, even if it came from your own database. Who cares where it came from... if you cannot predict what the data is, you know it isn't usable directly in a query.

On XSS attacks... you can prevent some of these by properly escaping data for use in an HTML context if you are outputting HTML pages. This allows you to use arbitrary strings in the context of HTML where the text is preserved. This escapes the data for HTML, meaning that the text won't be parsed as HTML tags. You should only do this escaping on output... not before, or you mangle your data early and make it unusable for other purposes.

Answer 2

BUT this may lead you prone to second order SQL injection

This may, actually, not. There is no such thing like "second order SQL injection". There is just SQL injection only. To prevent it you have to use parameterized queries. As simple as that.

To summarize, your steps would be

$string ='whatever string';
$pdo->prepare('any query that uses ? placeholder for any data');
$pdo->execute([$string]);

while ouputting make your template to do html escaping for any value by default, or apply any other format if told explicitly - i.e. make it raw for the html formatted texts.

Answer 3

This is standard procedure if you can not avoid getting raw input from user, if at all possible avoid using raw input from user. For example:

Best to use stored procedures for these types of things.

<?php
  if(isset($_POST['submit'])) {
      if($_GET['sort'] == 'alphad') {
          $sort = 'alphad'; //not = $_GET['sort']
          //Your query
      }
  }
?>