PHP: Injection protection using prepared statements

Question

Hi,

I am familiar with using PHP to perform mySQL queries. However, I have been using reg exps as protection against injection attacks. After reading several questions/answers here on SO, I've decided to opt for prepared statements instead.

There's two options available (let me know if there are more):

1. mysqli prepared statements
2. PDO prepared staments

Question 1

I am trying to understand the code examples given on the linked pages.

For mysqli, Example #1 :

if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {
    $stmt->bind_param("s", $city);

What does the "s" parameter do?
If I need more than 1 paramater, how do I do that?

For PDO, Example #1 :

$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

What is the purpose of PDO::ATTR_CURSOR and PDO::CURSOR_FWDONLY here?

Question 2

Which one, mysqli or PDO, would you recommend? Pros and cons?

Answer 1

Question 1

The s parameter binds ":" to whatever value $city has. So if your sql is "SELECT District FROM City WHERE Name = s", your executed query would be "SELECT District FROM City Where Name = $city".

To bind more parameters, just call bindParam for each parameter. You can also pass an array to PDOStatement::execute.

Question 2

Since i use some different databases (mysql and sqllite) i prefer working with PDO. Fore more information on this subject, please refer to http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons.

Answer 2

The s indicates that $city is expected to be a string

The PDO::ATTR_CURSOR part is the name of a setting you are passing to PDO. The value PDO::CURSOR_FWDONLY (which is the default, so you don't need to specify if) means that for each call you do to PDOStatement::fetch() you will be given the next row in the result set. The alternative option would be PDO::CURSOR_SCROLL - but unless you specifically know you need this (and your database supports it) you should leave it at the default.