Getting the syntax of prepared statements right

I am finding it necessary to work in an area that I have very little knowledge in. I have to secure a website database that uses about 20 MySQL SELECT calls to supply information to website visitors. The calls all take the form of:

$leadstory = "-1";
if (isset($_GET['leadstory'])) {
  $leadstory = $_GET['leadstory'];
}

$query_News = "SELECT * FROM news WHERE lead_story = $leadstory";
$News = mysql_query($query_News, $HDAdave) or die(mysql_error());
$row_News = mysql_fetch_assoc($News);
$totalRows_News = mysql_num_rows($News);

In a previous post I asked if these SELECT statements were vulnerable to sql insertion attacks. The answer was yes as you all probably know. I have done some reading and understand that I need to use mysqli and something like the following:

$statement = $db_connection->prepare("SELECT * FROM news WHERE lead_story = ?;';");
$statement->bind_param("s", $leadstory);
$statement->execute();
$row_News = $statement->fetchAll();

I have a few questions.

Do I need an "or die" type line if the connection fails?
How do I assign $totalRows_News?
Do I also need to "clean" the $leadstory variable with mysql_real_escape_string?

回答1:

Using PDO would make your life so much easier

$pdo = new PDO(...);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('SELECT * FROM news WHERE lead_story = ?');
$stmt->bindParam(1, $leadStory);
$stmt->execute();

$allRows_News = $stmt->fetchAll();
$totalRows_News = count($allRows_News);

回答2:

Don't use or die when outputting HTML. You'll get invalid HTML. Often, errors are detected at a low level but can only be handled at a higher level. Proper error handling involves passing the error along until it can be handled appropriately. You can use return values to hold errors (if there can be distinct error and non-error return types) or use exceptions.
You can get the number of result rows using mysqli_result->num_rows or PDOStatement->rowCount. Note the latter isn't available for all databases supported by PDO, but is for MySQL. However, both require buffered queries, which is less performant both because the query must finish before the program can continue (i.e. execute is synchronous with respect to the query) and the entire result set must be stored in memory. The method Phil shows will work with other databases, though it suffers the same performance costs as buffered queries. The alternative, unbuffered queries (execute is asynchronous, relative to the query) is to work with rows as they become available, ignoring the total number of rows until the end. PDOStatement supports Traversable, meaning you can loop over it with a foreach loop, so you don't need to know the total number of rows to loop over.
```
...
$query->execute(array(':after' => $date));
foreach ($query as $row) {
    ...
}
```
This makes processing results in other modules particularly nice, as they don't need to be aware of what they're iterating over. You can even have the results returned as instances of a given class.
```
$query->execute(...);
/* assign properties after calling constructor, so default property
   values set in constructor don't overwrite the values from the DB */
$query->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, 'Article');

# elsewhere, $articles has been set to the value of $query
foreach ($articles as $article) {
    # $article is an Article; do with it what you will.
    ...
}
```
The one disadvantage to using a PDOStatement directly is that query results generally have a one-time use; unless you're using a cursor, you can only loop over them once.
This question has been asked (likely many times) before: When is quoting necessary in prepared statements of pdo in PHP?

You can think of prepared statements as analogous to functions. With a function, you take a block of code, parameterize part of it and package it. A function can be called multiple times but only needs to be defined once. The same is true of prepared statements. Since values are kept separate from code, injection isn't possible in the parameters of a prepared statement (injection is the result of confusing data with code).

As with functions, you can't arbitrarily replace portions of a statement with a parameter. You need to respect syntax, for one thing. Another restriction is only certain kind of things can be parameterized. Both functions and parameters generally only let you parameterize values, though in some languages what counts as a value is rather broad. In SQL, what counts as a value is quite narrow. Identifiers (names for databases, tables, columns, stored procedures, &c) aren't values. Lists of values (such as the right argument of an IN operator) are not themselves values.

Expanding on one of the points in 1. a little, in a well-designed project, the code is compartmentalized into different modules based on exactly what features it implements. This is known as "separation of concerns" and leads to things like MVC and multi-tiered architectures. The "single responsibility principle is related (it could be considered a sub-principle). Applying these to the case at hand, you should have a distinct data access layer that's responsible for database access. There are various patterns you can apply to implement this, but the essential aspect is nothing but the DAL should access the database, or be affected by changing how data is persisted. The DAL may handle certain errors itself (partially or totally), but it shouldn't be concerned with user interaction, or with displaying data.