« | »

more with PHP; PEAR

By Lennard | April 30, 2006


Warning: preg_match() [function.preg-match]: Compilation failed: unrecognized character after (?< at offset 3 in /home/bakkerl/domains/lannerd.nl/public_html/wordpress/wp-content/plugins/codesnippet/lib/geshi.php on line 2132

Warning: preg_match() [function.preg-match]: Compilation failed: unrecognized character after (?< at offset 3 in /home/bakkerl/domains/lannerd.nl/public_html/wordpress/wp-content/plugins/codesnippet/lib/geshi.php on line 2132

Warning: preg_match() [function.preg-match]: Compilation failed: unrecognized character after (?< at offset 3 in /home/bakkerl/domains/lannerd.nl/public_html/wordpress/wp-content/plugins/codesnippet/lib/geshi.php on line 2132

Warning: preg_match() [function.preg-match]: Compilation failed: unrecognized character after (?< at offset 3 in /home/bakkerl/domains/lannerd.nl/public_html/wordpress/wp-content/plugins/codesnippet/lib/geshi.php on line 2132

Warning: preg_match() [function.preg-match]: Compilation failed: unrecognized character after (?< at offset 3 in /home/bakkerl/domains/lannerd.nl/public_html/wordpress/wp-content/plugins/codesnippet/lib/geshi.php on line 2132

Warning: preg_match() [function.preg-match]: Compilation failed: unrecognized character after (?< at offset 3 in /home/bakkerl/domains/lannerd.nl/public_html/wordpress/wp-content/plugins/codesnippet/lib/geshi.php on line 2132

Some of the forum of which I am a member of, are about developing websites, most are Linux, Apache, MySQL and PHP (LAMP). Some of the questions are about querying the database. Most of the time examples are given so other can see what may go wrong.

Example 1 (Dutch forum, so Dutch variables and output):

  1. $optie1 = $_POST[‘optie1′];
  2. $optie2 = $_POST[‘optie2′];
  3. $optie3 = $_POST[‘optie3′];
  4.  
  5. mysql_query("INSERT INTO gegevens (optie1, optie2, optie3) VALUES (‘".$optie1."’, ‘".$optie2."’, ‘".$optie3."’)")
  6.   or die(mysql_error());
  7. echo "Je hebt een order toegevoegd";

In this example the $_POST variables are read and unchecked and unescaped used in the query. I would guess that this person never heard of SQL-injection.

Example 2 (Again Dutch forum so Dutch output):

  1. if(!mysql_query("UPDATE videos SET views = (views + 1) ,last_view = ‘". date("Y-m-j H:i:s", time()) ."’ WHERE id = ". htmlspecialchars($_GET[‘id’]) .""))
  2. {
  3.   echo "<br />Er is een fout opgetreden: bijtellen van views is mislukt.";
  4. }

In this example the input is escaped so SQL-injection is filtered. But within this query you still need to explicit take care of the ‘’ (quotes) around the strings.

In these two and many other examples on the forums i never see much code which uses PEAR. “PEAR is a framework and distribution system for reusable PHP components”.
Is this that no many know about PEAR (http://pear.php.net) of just not willing to use PEAR?

An example of the select query with PEAR (and opening the connection to the database).

  1. require_once("DB.php");
  2.  
  3. // Setup the connection
  4. $db = DB::connect("mysql://username:password@hostname:tablename");
  5. $db->setFetchMode(DB_FETCHMODE_ASSOC);
  6.  
  7. // Create and execute query
  8. $sql = "SELECT * FROM tabel WHERE id=? and datefield BETWEEN ? AND ?";
  9. $sqldata = array($id, $start_datum, $end_datum);
  10. $rows = $db->getAll($sql, $sqldata);
  11.  
  12. if (DB::IsError($rows))
  13.   die("There in an error: ".$rows->getMessage());

After this piece of code, all selected rows are in $rows. $rows is an array and can be directly used in PHP. No need to loop thru the results of the query and assign the values to an array. The ‘?’ (question mark) in the query string stands for the variables which are needed to fill in. These variables are put in an array (in the right order) and the query can be done. The code behind it will escape the variables if needed. If a field is required to be quoted in the query string, the quotes will be added. No more worries about SQL injection.

A few other advantages:
- The connection string starts in this example with “mysql”. There can be replaced with other databases. Normally the code has to be rewritten if an other database is used. As seen in the first examples the method mysql_query() is used. This only works with a MySQL database, for a ProgreSQL database it would be pg_query() method. By using PEAR the queries don’t have to be rewritten, only the connection string would change.
- Beside the method getAll() which returns a array with all rows there is also a getOne() which only returns a array with only the first row. If you want to use the old ways and want to loop to the results yourself there is the method Query().

Is PEAR just unknown to unknown or are the reasons why not to use PEAR?

The first reason was: PEAR isn’t always installed on a hosting environment.
PEAR don’t need to be installed global on a system. It is easier way for use, but PEAR can also be installed in a subfolder of your project.

Topics: Just me, php | No Comments »

Comments