Looking for more information on how to do PHP the right way? Check out PHP: The Right Way

SitePoint PHP Blog:
Re-introducing PDO – the Right Way to Access Databases in PHP
Aug 25, 2015 @ 16:10:14

On the SitePoint PHP blog they have a post that "reintroduces PDO" or as they describe it, the "right way to access databases in PHP". The PDO functionality in PHP provides extra handling around database connections and queries as well as making it easier to connect to multiple types of databases with similar code.

PDO is the acronym of PHP Data Objects. As the name implies, this extension gives you the ability to interact with your database through objects. [...] PHP is rapidly growing, and it is moving toward becoming a better programming language. Usually, when this happens in a dynamic language, the language increases its strictness in order to allow programmers to write enterprise applications with peace of mind.

In case of PHP, better PHP means object-oriented PHP. This means the more you get to use objects, the better you can test your code, write reusable components, and, usually, increase your salary. Using PDO is the first step in making the database layer of your application object-oriented and reusable.

He starts by answering the question most ask about PDO versus mysql/mysqli by pointing out that PDO is more OOP friendly, it allows for parameter binding and the fact that the mysql extension is no longer supported. He shows how to check and ensure PDO is installed on your setup and, if not, how to add it in (for both linux and Windows systems). The tutorial then walks you through using PDO: making the connections to the server, running queries and returning the results. This includes a section on prepared statements and bound parameters and their benefits including SQL injection prevention.

tagged: pdo database access tutorial introduction prepared statements phpdataobjects

Link: http://www.sitepoint.com/re-introducing-pdo-the-right-way-to-access-databases-in-php/

Code Yellow Blog:
What Your Framework Never Told You About SQL Injection Protection
May 23, 2014 @ 18:51:20

The Code Yellow site has recently posted an article pointing out an issue that's all too common in PHP frameworks, more specifically those that bundle some kind of ORM into their functionality. They wonder if your framework is telling you everything about what they're doing to prevent SQL injection.

We've discovered that SQL injection is to this day not a fully solved problem, even in most popular frameworks. In this post, we'll explain how these frameworks fail at escaping parts of a query, culminating in the discovery of a critical vulnerability in the popular Laravel framework which affects a large percentage of applications.

He starts with an illustration using the FuelPHP framework and the protection it offers from garden variety SQL injection attempts. Unfortunately, things start to break down when it gets much past this typical case. They found this same issue to be a wide-spread problem in many PHP frameworks and tools including the Laravel, CodeIgniter and CakePHP frameworks, each with their own ORMs. He also talks about issues with blacklisting and whitelisting and how, sadly, most of the frameworks just don't support it for model data filtering. There's a mention of some of the work they've done to help try and fix the issue (including patches and contacting authors) and some recommendations of how to correctly quote identifiers in SQL statements.

tagged: sqlinjection framework whitelist blacklist identifiers escape prepared statements

Link: http://www.codeyellow.nl/identifier-sqli.html

Evert Pot:
MySQL 5.6 BOOL behavior when using PDO and prepared statements
Dec 05, 2013 @ 16:37:42

Evert Pot was seeing some weird issues with his MySQL BOOL usage via PDO when he upgraded to one of the latest versions (5.6). Thankfully, he's shared his solution to the problem as well as the symptoms he was seeing when it was causing problems.

I recently updated my workstation to run MySQL 5.6.13. It didn't take very long for things to start breaking, and since I couldn't find any other information about this on the web, I figured this may be useful to someone else. The main error that started popping up was: "Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'my_bool' at row 1' in test.php" This exception happens under the condition that you use PDO, prepared statements and booleans.

He includes a small sample script to reproduce the issue and points out the issue - the default casting of prepared values to strings in prepared statements with PDO bound parameters. He shows two "relatively easy solutions" to the problem - either using integers instead of the true/false PHP boolean or specifying a type with the bindValue call.

tagged: mysql upgrade boolean field pdo prepared statement

Link: http://evertpot.com/mysql-bool-behavior-and-php/

Anthony Ferrara:
Programming With Anthony - Prepared Statements
Dec 13, 2012 @ 17:50:22

Anthony Ferrara has posted about the latest installation in his video tutorial series he's been producing on various programming topics. In this latest video he covers the use of prepared statements in your database interactions.

The fourth video in the Programming With Anthony series is live! In this video, we'll explore the basic principles of prepared statements, and why you should use them instead of using escaped input directly in queries.

You can watch this latest video over on YouTube. You can also check out the previous videos in the series while you're there covering paradigms, encryption and references.

tagged: prepared statements video tutorial series anthonyferrara

Link:

Volker Dusch's Blog:
References suck! - Let's fix MySqli prepared statements!
Jun 14, 2011 @ 16:46:55

Volker Dusch has a new post to his blog looking at the use of references in PHP (or lack there of) and what we, as end users of the language, can do about it. His example looks at mysqli prepared statements.

Even so not every PHP Developers knows WHY we don’t use references pretty much every core function and every somewhat modern framework avoids them so people adapted this best practice. The leftovers in the PHP core, like sort() or str_replace(), are exceptions to the rule. So if the common consensus is, or at least 'should be', that we should not use references then maybe we should start looking for places where they hurt and how we could fix them?

He talks about prepared statements and one thing he sees that makes it a "hard sell" to developers needing a good way to query their databases. He points out the difference in code required between the normal MySQL calls and mysqli (hint: it's more) and shows how to use an abstraction layer to make things a bit easier. He points out the downfalls of using this approach, mainly the performance hit you get (from using his fetchAll method).

tagged: references mysqli prepared statement performance abstraction

Link:

Johannes Schluter's Blog:
Escaping from the statement mess
May 19, 2011 @ 14:30:45

In a new post to his blog Johannes Schluter suggests an alternative to using prepared statements in PHP applications using a database - creating a handler method that allows for dynamic queries as well as proper escaping of values.

Now prepared statements were a nice invention some 30 years ago abut they weren't meant for making things secure and so they do have some shortcomings: One issue is that preparing and executing a query adds a round-trip to the server where it then requires resources. [...] With prepared statements you first have to build the list of place holders (the exact amount of place holders (?) separated by a comma, without trailing comma) and then bind the values and mind the offsets when having other values - this typically becomes ugly code.

He includes the code for his alternative, a function using the mysqli extension to let you create dynamic SQL that still uses placeholders and proper escaping to prevent both SQL injection issues and resources problems caused by the multiple hops back to the database.

tagged: prepared statement database alternative mysqli

Link:

NETTUTS.com:
The Problem with PHP’s Prepared Statements
Aug 09, 2010 @ 15:09:01

On NETTUTS.com there's a new tutorial talking about the problem with PHP's prepared statements, mainly due to their flexibility.

There are a couple issues that appear to make these methods less flexible than we’d hope. For one, we must utilize the bind_result method, and pass in a specific number of variables. However, what happens when this code is within a class, and we won’t immediately know how many variables to pass? Luckily, there’s a solution! I’ll show you what it is in today’s video tutorial.

The tutorial is screencast but they've also included the full code ready for cut and pasting into your favorite editor of choice.

tagged: tutorial screencast prepared statement

Link:

Carson McDonald's Blog:
PHP MySQLi and Multiple Prepared Statements
Feb 15, 2010 @ 19:29:28

When Carson McDonald tried to get multiple prepared statements to work in his MySQLi code for his application, he got a "commands out of sync" error. Luckily, he's found a solution thanks to the store result.

Details about this error can be found in the mysql docs. Reading those details makes it clear that the result sets of a prepared statement execution need to be fetched completely before executing another prepared statement on the same connection.

He gives code snippets that are "before" and "after" examples of what he had to change to get things working. Each time its executed, the "store_result" call is made and the result set is pulled out of the prepared statement.

tagged: prepared statement tutorial storeresult mysqli

Link:

Greebo.net:
Converting your PHP app to MySQLi prepared statements
Jan 04, 2010 @ 19:46:13

From Greebo.net there's a recent post that looks at converting the current database functionality in your application over to the MySQLi functionality and making use of prepared statements as a later of protection for your queries.

Okay, you’ve got like a zillion SQL queries in your PHP app, and probably 95% of them have a WHERE clause, and you need to make them safe so people will still download and use your app. Because if you don’t fix your injection issues, I will rain fire on your ass. These are the steps you need to take to convert to prepared statements.

The guide is two steps you'll need to make the transition - "PHP 4 is dead. Upgrade to PHP 5" and "make sure your hoster has MySQLi". The major part of the update is under the first point where he gives code examples and suggestions to follow about how to "harden" your environment to prevent and issues that lax SQL methods might have caused and a simple example of a move from MySQL to MySQLi.

tagged: mysql mysqli convert prepared statements tutorial

Link:

Rubayeet Islam's Blog:
MySQL Prepared Statements and PHP : A small experiment
Oct 30, 2008 @ 16:13:58

In a recent post to his blog Rubayeet Islam compared the more traditional way of running a query in MySQL versus a prepared statement with the MySQLi extension.

Consider a PHP-MySQL application where the information of 1000 users is being retrieved from the database by running a for loop [...] in each iteration, the first thing the MySQL engine does is to parse the query for syntax check. Then it sets up the query and runs it. Since the query remains unchanged during each iteration(except for the value of user_id), parsing the the query each time is definitely an overhead. In such cases use of prepared statements is most convenient.

He explains what prepared statements are and some of the advantages around them and includes some benchmarking examples to show the differences - about a five second jump in favor of MySQLi.

tagged: mysqli prepared statement tutorial benchmark

Link:


Trending Topics: