0

Sorry, for my bad English!

As I'm learning PHP and having some questions about insert and output data from the database.

I am using PHP PDO.

To insert data to the database I'm using following function:

public static function validate( $string ){
    $string = trim($string);        
    $string = htmlspecialchars($string, ENT_QUOTES, 'UTF-8');
    return $string;
}

So when I insert this data O'Really <script>alert(is it safe?)</script> I see the data is properly(maybe) escaped/saved in the database. like that: &lt;script&gt;alert(1)&lt;/script&gt;

Now, When I output this data should I use any PHP function?

If not then Is it safe?

Okay, If I use any PHP function like htmlentities then the data is showing like that O&#039;Really &lt;script&gt;alert(is it safe?)&lt;/script&gt;

Off course which I don't want.

Now, when I edit this data I see the data is saved to the database like this way:

O&amp;#039;Really &amp;lt;script&amp;gt;alert(is it safe?)&amp;lt;/script&amp;gt;

Can you guys tell me the proper way / guide to safely insert/output data to/from the database?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Shibbir Ahmed
  • 161
  • 2
  • 12
  • 1
    If you're the only editor on the website and don't have any worries about someone else adding something suspecious, Then that enough, If not, check this Sheet https://www.owasp.org/index.php/XSS_(Cross_Site_Scripting)_Prevention_Cheat_Sheet., Also, It is better not to save a tag inside the table, But add the tag when you call the data. – AXAI Oct 06 '17 at 17:48
  • 3
    Everything about this screams "‼️" Trimming and escaping are two totally different concerns. Trim and clean up on input, escape if **and only if** you're displaying. For HTML you use HTML escaping functions, for JSON and JavaScript it's different. When writing to the database make every effort to **use prepared statements with placeholder values**. – tadman Oct 06 '17 at 17:55
  • your issue has nothing to do with sql or injections. – Your Common Sense Oct 06 '17 at 18:58
  • You should just use one of htmlspecialchars() and htmlentities(), not both. Using both of them leads to double-encoding. Cfr https://stackoverflow.com/questions/46483/htmlentities-vs-htmlspecialchars – UML Oct 06 '17 at 20:44

1 Answers1

0

There are (at least) two different risks you want to handle while storing user-given data from a web page in a database:

  1. Cross Site Scripting (XSS) attacks, as AXAI mentioned above. In this scenario the problem isn't actually the database layer, but the dynamic text fields that are inserted into the HTML code. In your code snippet, you handled this problem by turning the tag marks (< and >) into entities before you stored them in the database. I recommend doing the opposite (as tadman says): storing the plain text untouched (but see next section), and use the htmlspecialchars() when outputting the fields in the HTML output.

  2. SQL injection attacks. Basically, you want to escape any special characters correctly, e.g. ' must be turned into \' in a SQL command. If this escaping is done correctly it does not distort what is saved in the database, but assures that exactly all of the characters (whether normal or special) input by the user are put in the database. The article http://php.net/manual/en/security.database.sql-injection.php describes this closer, and also gives event better methods (i.e. variable binding).

UML
  • 146
  • 8
  • So, for the first point, you are talking about it insert this data `O'Really ` directly without any escaping to the database table and use `htmlspecialchars()` when output, right – Shibbir Ahmed Oct 07 '17 at 03:22
  • Yes, putting it without any HTML-style escaping into the database. **However**, depending on how you put it into the database, you possibly need to do SQL-specific escaping: `INSERT ... ('O\'Really – UML Oct 09 '17 at 07:05