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?

@InsaneCoder Your approach is a mess... and a common mistake I see time and time again in code everywhere. See my answer for some information, and if you have questions on it, ask them specifically. — Brad, Jan 25 '14 at 07:23

score 3 · Answer 1 · answered Jan 25 '14 at 07:19

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.

Thanks for your support.But perhaps I cannot use `htmlentities()` in output as I have articles stored in my database in HTML form like `
Title of the article
This is an artcle
so I want such tags to be parsed. — Naveen, Jan 25 '14 at 07:29
Look into using BBCode in regards to this so you can avoid using html tags — sunshinekitty, Jan 25 '14 at 07:31
@InsaneCoder In that case you don't have an escaping problem. (You're using HTML in an HTML context... no escaping necessary.) You have a different problem where you need to whitelist what tags are acceptable to you, but you need to be very careful about this to not let syntax errors let executable code to creep in. Check out this post: http://stackoverflow.com/questions/5512712/sanitizing-html-input And no, you don't necessarily need to use BBCode... changing your syntax doesn't fix this problem. — Brad, Jan 25 '14 at 07:34

Your Common Sense · Answer 2 · 2014-01-25T08:03:35.953

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.

score -1 · Answer 3 · answered Jan 25 '14 at 07:21

-1

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
      }
  }
?>

answered Jan 25 '14 at 07:21

sunshinekitty

2,307
6
19
31

Sure, if your application does nothing then go ahead and not accept data from the user. And, stored procedures are not an appropriate answer to this problem. – Brad Jan 25 '14 at 07:22
Don't believe I ever stated to not allow data from user, this is the best procedure when dealing with arbitrary data like this. – sunshinekitty Jan 25 '14 at 07:24
It isn't. In fact, your code example does nothing to deal with arbitrary data. You're white-listing a specific set of data. While I agree that a white-list is great, it rarely applies. I'm saying prepared queries are absolutely what you should be using. – Brad Jan 25 '14 at 07:25

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

3 Answers3