Welcome!

Join our community of MMO enthusiasts and game developers! By registering, you'll gain access to discussions on the latest developments in MMO server files and collaborate with like-minded individuals. Join us today and unlock the potential of MMO server development!

Join Today!

Best way to sanitize input SQL

Joined
Dec 16, 2011
Messages
1,993
Reaction score
631
Hello, I am coding a comment system for my website and I want to ensure a user-friendly comment section while not limiting them from posting comments that use ' & "... This is what I've got:

PHP:
$comment = $db->real_escape_string($_POST['comment']);

Is this appropriate input filter?

How about

PHP:
htmlentities(stripslashes($comments['comment']))

How do I test the integrity of the code to ensure it can't be exploited?
 
Joined
May 23, 2008
Messages
1,071
Reaction score
574
Hey Liam, the best way to prevent SQL injection is not to focus on sanitizing input - it's never full proof. Rather than that, it's actually industry standard and strongly recommended to use parametrized queries, also known as prepared statements.

When you're just sanitizing your input, the data (potentially from user input) is being treated as part of the query, which is always dangerous for various reasons. When you use prepared statements, the data is treated separately from the query itself - it's essentially treated as data to be inputted into the database, which is much safer and more sensible.

See or for the technical implementation in PHP, and check out or for more general information about prepared statements.
 
"(still lacks brains)"
Loyal Member
Joined
Sep 2, 2011
Messages
2,371
Reaction score
1,361
Like TimeBomb said, prepared statements are always the way to go. Considering it's 2017 you SHOULD be using prepared statements regardless. If you're allowing users to embed images, use different font styling and font sizing then you'll run into issues using anything but.

I'd also recommend using a popular framework such as CakePHP or Laravel rather than writing an entire system from scratch to prevent the most common attacks since they are tried and tested. While this may be a major rewrite for your system, it is highly recommended.
 
Super-Moderator
Staff member
Super-Moderator
Joined
Apr 28, 2007
Messages
1,495
Reaction score
756
PHP:
htmlentities(stripslashes($comments['comment']))
This is not an 'input filter' but an 'output filter'. This is code which (partly) filters XSS injections, not a SQL Injection (not even close). As @TimeBomb and @NoBrain mentioned: you could use prepared statements. However, you'll still need to filter out XSS injections in the output.

The reason why you do not want to (XSS) filter your data before inserting it into the database, is because you might want to keep the raw data for other reasons (such as editing the data, or showing the actual data on different places without a XSS filter). As a XSS filter I highly recommend to use HTML Purifier. This is being used in many CMS'. You (unless you're an extremely well educated XSS expert) cannot make it as 'waterproof' as possible. That's why I recommend you to use HTML Purifier: . Especially since you mentioned this is for a comment page on your website.
 
Skilled Illusionist
Joined
Jun 21, 2010
Messages
324
Reaction score
54
Like everyone has said, I would definitely recommend using prepared statements. In addition to all security features and things like that it's also really awesome and easy to modify these statements and execute again without modifying the actual statement itself.

Example of using prepared statements in PHP:
PHP:
<?php
$mysqli = new mysqli("localhost", "root", "", "test");

$stmt = $mysqli->prepare("INSERT INTO users (username, password, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $username, $password, $email);

$username = "academic";
$password = password_hash("password123", PASSWORD_DEFAULT);
$email = "email@example.com";

$stmt->execute();

$username = "MentaL";
$password = password_hash("password1234", PASSWORD_DEFAULT);
$email = "mental@example.com";

$stmt->execute();

$stmt->close();
$mysqli->close();
?>
 
Back
Top