My Approach while INSERT’ing INTO MySql
I think I read in stackoverflow.com that “if you need escaping or similar action, do it just in time you need” so in the verification pages that I verify the user inputs (null or not check, length check and structural checks (eg: mail structure, custom tags structures); I use the $_POST[''] variables as inputs. During verifications, even in the custom error printing parts, my error messages does not include any of $_POST[''] values in message texts.
As an interim note: I utilize prepared statements and parameterized queries during php-MySql interactions. If inputs are verified; just before INSERT’ing INTO MySql, I strip the tags from input since I don’t allow any html tags other than custom structured tags. (for example **bold text** === <strong>bold text</strong>) Then I insert the user input into MySql db.
My Approach while fetching from MySql & printing the output to the screen
I only apply htmlspecialchars() command to print out to the screen from MySql db
My Question
I am not sure of myself. Is there any obvious or hidden weakness in my approach? Thanks in advance for php gurus’ valuable comments. BR
UPDATE
I won't strip tags during insert into MySql db. For reasons, Please refer to comments of ÁlvaroG.Vicario below. BR.
The discussion thus far has been about protecting from SQL Injection and Persistent cross site scripting. It sounds like you're on the right track.
- Your use of prepared statements is a "best practice" to combat SQL injection.
- htmlspecialchars() is a good start to prevent XSS, but you have to escape data in the encoding scheme that is appropriate to where you are outputting data. OWASP has a comprehensive page that discusses this: XSS (Cross Site Scripting) Prevention Cheat Sheet. The short answer: Ensure you are using "
the escape syntax for the part of the HTML document you're putting untrusted data into."
I tried code below in trial.php
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="tr">
<head>
<title>trial</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<meta http-equiv="Content-Language" content="tr" />
<meta name="Description" content="trial page." />
<meta name="Robots" content="noindex, nofollow" />
</head>
<body>
<?php
$str1 = '<script>alert(\'inside of input with quote\')</script>';
$str2 = '<script>alert("inside of input with quote")</script>';
$str3 = "<script>alert(\"inside of input with quote\")</script>";
$str4 = '<script>alert("outside of form")</script>';
?>
<form method="post" action="">
<fieldset>
<legend>alert attempts inside form inputs</legend>
<label for="input1">label 1:</label><br />
<input type="text" value="<?php echo htmlspecialchars($str1) ; ?>" name="input1" id="input1" /><br /><br />
<legend>attempt 2</legend>
<label for="input2">label 2:</label><br />
<input type="text" value="<?php echo htmlspecialchars($str2) ; ?>" name="input2" id="input2" /><br /><br />
<label for="input3">Label 3:</label><br />
<input type="text" value="<?php echo htmlspecialchars($str3) ; ?>" name="input3" id="input3" />
</fieldset>
</form>
<?php echo htmlspecialchars($str4) ; ?>
</body>
</html>
results of trial.php
- what the user cared typing didn't break, so user data did not
corrupted
- I see exactly what the user cared typing on screen
- script alerts did not work
resulted procedure while INSERT’ing INTO MySQL & fetching from MySQL and printing the output to screen
- utilize prepared statements and parametric queries during
php-MySQL interactions. while INSERT’ing INTO MySQl db, this approach prevents SQL Injections already so no need to extra escaping. Extra effort like
strip_tags() will disrupt what the user exactly cared typing just like htmlspecialchars() OR htmlentities() will do also.
- utilize prepared statements and parametric queries also while
fetching from MySQL db
- while printing user inputted data onto screen, use
htmlspecialchars() so what the user exactly cared typing won't
break and possible malicious special characters will be converted to
their corresponding HTML entities
