3

I have a simple comment system, user inputs text into textarea, a PHP script runs, checks if user is logged in, if submit is pressed and everything is filled out - it goes on inserting the data. My question is, what do I need to escape/trim/strip? Right now my query looks like this:

$sql = $con->prepare("INSERT INTO Comments (user, comment, pageid, time) VALUES (:user, :comment, :pageid, NOW())");
$sql->bindValue(":user", $user, PDO::PARAM_STR);
$sql->bindValue(":comment", $comment, PDO::PARAM_STR);
$sql->bindValue(":pageid", $pageid, PDO::PARAM_INT);
$sql->execute();

The variables come from the form using the POST method. Is this secure to sql injection or do I need to do some extra trimming and escaping before inserting the data?

ejx
  • 469
  • 1
  • 6
  • 20
  • 3
    Yes, this is sufficient to prevent SQL injection. You may be interested in [The Great Escapism (Or: What You Need To Know To Work With Text Within Text)](http://kunststube.net/escapism/). – deceze Feb 18 '13 at 12:43
  • 2
    Read: http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php – Daniel Feb 18 '13 at 12:46
  • 2
    There are actually two parts to this equation. Your question deals with the first part, storing data and you are getting good information. The second part concerns retrieving and displaying it. If someone writes some html or js into your textarea, and you display it on a webpage, it will execute unless you do something to prevent. If php has anything equivalent to ColdFusion's htmleditformat(), use it. – Dan Bracuk Feb 18 '13 at 12:50
  • 1
    @DanBracuk, the data is in most cases not escaped until output - but this function escapes special characters http://php.net/htmlspecialchars – Daniel Feb 18 '13 at 12:54
  • Yeah I thought of the output forming a possibly harmful script and I made sure everything is escaped. – ejx Feb 18 '13 at 12:57

1 Answers1

3

As long as you're using PDO with prepared statements and placeholders, you're practically as safe as can be.

I would recommend though to use ->execute() with an array, it's much simpler than manually binding variables.

$sth = $con->prepare('INSERT INTO Comments (user, comment, pageid, time) VALUES (?, ?, ?, NOW())');

$sth->execute(array($user, $comment, $pageid));
silkfire
  • 24,585
  • 15
  • 82
  • 105
  • 1
    ±1 It may be simpler, but binding parameters is better for type safety. You can't specify the `PDO::PARAM_*` constants in `execute()`. – deceze Feb 18 '13 at 12:47
  • I see, so as long as I prepare them I'm safe. Thanks for the array() tip, that does make things easier! – ejx Feb 18 '13 at 12:49