Getting the correct syntax for prepared statements

Question

I consider it necessary to work in an area in which I have very little knowledge. I have to provide a website database that uses about 20 MySQL SELECT queries to provide information to website visitors. All calls take the form:

$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 are vulnerable to attacks on sql inserts. The answer was yes, as you probably know. I read and realized 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.

• Is a type string or die needed if the connection fails?

• How to assign $ totalRows_News ?

• Do I also need to " clear " the $leadstory variable with mysql_real_escape_string ?

Answer 1

Using PDO will make your life a lot 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); 

Answer 2

• You cannot use or die in HTML output. You will receive invalid HTML. Often errors are detected at a low level, but can only be handled at a higher level. Correct error handling involves transmitting the error until it is properly processed. You can use return values ​​to hold errors (if there can be different types of errors and errors without errors) or use exceptions .

• You can get the number of rows of results using mysqli_result->num_rows or PDOStatement->rowCount . Please note that the latter is not available for all databases supported by PDO, but for MySQL. However, both require buffered queries, which is less efficient since the query must complete 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 Phil method shows working with other databases, although it suffers the same performance costs as buffered queries. Alternative, unbuffered queries ( execute asynchronous with respect to the query) is to work with strings as they appear, ignoring the total number of rows to the end. PDOStatement supports Traversable , which means you can PDOStatement over it with a foreach , so you don't need to know the total number of lines to iterate over.

   ... $query->execute(array(':after' => $date)); foreach ($query as $row) { ... } 

  This makes the processing results in other modules particularly enjoyable, since they do not need to know what they are repeating. You can even return results as instances of this 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 only drawback to using PDOStatement is that query results usually have a one-time use; if you do not use the cursor, you can scroll them only once.

• This question has been asked (probably many times) before: When is the need for prepared pdo statements in PHP indicated?

You can think of prepared statements similar to functions. Using the function, you take a block of code, parameterize part of it, and package it. A function can be called several times, but it needs to be defined only once. The same applies to prepared statements. Since the values ​​are stored separately from the code, injection is not possible in the parameters of the prepared statement (injection is the result of confusing data with the code ).

As with functions, you cannot arbitrarily replace parts of an instruction with a parameter. First, you need to respect the syntax. Another limitation may be the parameterization of only a certain type of thing. Both functions and parameters usually allow you to parameterize values, although in some languages ​​the value is considered quite wide. In SQL, what is considered a value is rather narrow. Identifiers (names for databases, tables, columns, stored procedures, and c) are not values. Lists of values ​​(such as the right argument of the IN operator) are not values ​​themselves.

Turning around at one of the points in 1. slightly, in a well-designed project, the code is divided into different modules, based on exactly what functions it implements. This is called " separation of concerns " and leads to things like MVC and layered architectures. Principle The " level of data access that is responsible for accessing the database." There are various patterns that you can apply to implement this, but the essential aspect is nothing, except that the DAL must access the database or be affected by a change in how the data is stored. DAL can handle certain errors (partially or completely), but this should not concern user interaction or data display.