Client 4 - An Interactive Query Processor

You now have most of the pieces that you need to build a general-purpose query processor within a web browser. Our next client simply prompts the user for a SQL command, executes the command, and displays the results.

If you want to try this on your own web server, be sure that you understand the security implications. If you follow the examples in this chapter, your PHP script will use a hard-coded username to connect to PostgreSQL. Choose a user with very few privileges. In fact, most PHP/PostgreSQL sites should probably define a user account specifically designed for web access. If you're not careful, you'll grant John Q. Hacker permissions to alter important data.

We'll start out with a simple script and then refine it as we discover problems.

First, you need an HTML page that displays a welcome and prompts the user for a SQL command. Listing 15.16 shows the client4.html document.

Listing 15.16 client4.html

 1 <HTML>


 3 <!-- Filename: client4.html>


 5   <HEAD>

 6     <TITLE>client4a</TITLE>

 7   <BODY>

 8    <CENTER>

 9    <FORM ACTION="client4a.php" METHOD="POST">

10      <I>Enter SQL command:</I><br>


12      <INPUT TYPE="text"

13             NAME="query"

14             SIZE="80"

15             ALIGN="left"

16             VALUE="">


18      <BR><BR>

19      <INPUT TYPE="submit" VALUE="Execute command">

20   </FORM>


22 </HTML>

This HTML document defines a form that will be posted to the server (see line 9). After the user enters a command and presses the Execute Command button, the browser will request the file client4a.php. We'll look at client4a.php in a moment. When you request this page in a web browser, you will see a form similar to that shown in Figure 15.9.

Figure 15.9. client4.html.


Now let's look at the second half of the puzzle?client4a.php (see Listing 15.17).

Listing 15.17 client4a.php

 1 <HTML>

 2   <HEAD>

 3     <TITLE>Query</TITLE>

 4   <BODY>

 5     <?php


 7       # Filename: client4a.php


 9       include( "secure/my_connect_pg.php" );

10       include( "my_table_e.php" );


12       $command_text = $HTTP_POST_VARS[ "query" ];


14       if( strlen( $command_text ) == 0 )

15       {

16         echo "You forgot to enter a command";

17       }

18       else

19       {

20         $db_handle = my_connect_pg( "movies" );


22         $table = new my_table( $db_handle, $command_text );

23         $table->finish();


25         pg_close( $db_handle );

26       }

27     ?>

28   </BODY>

29 </HTML>

Most of this script should be pretty familiar by now. You include secure/my_connect_pg.php to avoid embedding a username and password inline. Next, you include my_table_e.php so that you can use the my_table class (my_table_e.php includes all the modifications you made to the original version of my_table_a.php).

At line 12, you retrieve the command entered by the user from the $HTTP_POST_VARS[] variable. Look back at lines 12 through 16 of Listing 15.16 (client4.html). You are defining an INPUT field named query. When the user enters a value and presses the Execute Command button, the browser posts the query field to client4a.php. PHP marshals all the post values into a single associative array named $HTTP_POST_VARS[]. The key for each value in this array is the name of the posted variable. So, you defined a field named query, and you can find the value of that field in $HTTP_POST_VARS["query"].

If you try to execute an empty command using pg_query(), you'll be rewarded with an ugly error message. You'll be a little nicer to our users by intercepting empty commands at lines 14 through 16 and displaying a less intimidating error message.

The remainder of this script is straightforward: You establish a database connection and use the my_table class to execute the given command and display the result.

Let's run this script to see how it behaves (see Figures 15.10 and 15.11).

Figure 15.10. Submitting a query with client4.html.


Figure 15.11. Submitting a query with client4.html?result.


That worked nicely. Let's try another query (see Figures 15.12 and 15.13).

Figure 15.12. Causing an error with client4.html.


Figure 15.13. Causing an error with client4.html?result.


Hmmm… that's not what we were hoping for. What went wrong? Actually, there are several problems shown here. First, PHP is reporting that we have an erroneous backslash on line 12 of my_table_e.php. Line 12 is inside of the my_table constructor and it sends the following command to the server:

$this->result  = pg_query( $db_handle, $command );

There are no backslashes on that line; there are no backslashes in the command that you entered. Where are the backslashes coming from? If you echo $HTTP_POST_VARS ["query"], you'll see that PHP has added escape characters to the command entered by the user. You entered SELECT * FROM customers WHERE birth_date = '1984-02-21', and PHP changed this to SELECT * FROM customers WHERE birth_date = \'1984-02-21\'. According to the PHP manual, all single-quotes, double-quotes, backslashes, and NULLs are escaped with a backslash when they come from a posted value.[4]

[4] You can disable the automatic quoting feature by setting the magic_quote_gpc configuration variable to no. I would not recommend changing this value?you're likely to break many PHP scripts.

This is easy to fix. You can simply strip the escape characters when you retrieve the command text from $HTTP_VARS[]. Changing client4a.php, line 12, to

if( get_magic_quotes_gpc())

    $command_text = stripslashes( $HTTP_POST_VARS[ "query" ] );

will make it possible to execute SQL commands that contain single-quotes.

That was the first problem. The second problem is that you don't want the end-user to see these nasty-looking PHP/PostgreSQL error messages. To fix this problem, you need to intercept the error message and display it yourself. Listing 15.18 shows a new version of the my_table constructor.

Listing 15.18 my_table.my_table()

 1 function my_table( $db_handle, $command )

 2 {

 3   $this->result  = @pg_query( $db_handle, $command );


 5   if( $this->result == FALSE )

 6   {

 7     echo pg_last_error( $db_handle );

 8   }

 9   else

10   {

11     $this->columns = pg_num_fields( $this->result );

12     $row_count     = pg_num_rows( $this->result );


14     $this->start_table( $command );


16     for( $row = 0; $row < $row_count; $row++ )

17       $this->append_row( $this->result, $row );

18   }

19 }

We've restructured this function a bit. Because the goal is to intercept the default error message, you suppress error reporting by prefixing the call to pg_query() with an @. At line 5, you determine whether pg_query() returned a valid result set resource. If you are used to using PostgreSQL with other APIs, there is an important difference lurking here. In other PostgreSQL APIs, you get a result set even when a command fails?the error message is part of the result set. In PHP, pg_query()returns FALSE when an error occurs. You must call pg_last_error() to retrieve the text of the error message (see line 7).

If you have succeeded in executing the given command, you build an HTML table from the result set as before.

Now, if you cause an error condition, the result is far more palatable (see Figures 15.14 and 15.15).

Figure 15.14. Causing an error with client4.html?part 2.


Figure 15.15. Causing an error with client4.html?part 2, result.


Notice that you see only one error message this time. In Figure 15.13, you saw multiple error messages. Not only had you failed to intercept the original error, but you went on to use an invalid result set handle; when you fix the first problem, the other error messages will go away.

At this point, you can execute queries and intercept error messages. Let's see what happens when you execute a command other than SELECT. First, enter the command shown in Figure 15.16.

Figure 15.16. Executing an INSERT command.


After clicking on the Execute Command button, you see the result displayed in Figure 15.17.

Figure 15.17. Executing an INSERT command?result.


Hmmm… that's a bit minimalist for my taste. You should at least see a confirmation that something has happened. When you execute a non-SELECT command, the pg_query() function will return a result set resource, just like it does for a SELECT command. You can differentiate between SELECT and other commands by the fact that pg_num_fields()always returns 0 for non-SELECT commands.

Let's make one last modification to the my_table constructor so that it gives feedback regardless of which type of command executed.

Listing 15.19 my_table.my_table()?Final Form

 1 function my_table( $db_handle, $command )

 2 {

 3   $this->result  = @pg_query( $db_handle, $command );


 5   if( $this->result == FALSE )

 6   {

 7     echo pg_last_error( $db_handle );

 8   }

 9   else

10   {

11     $this->columns = pg_num_fields( $this->result );


13     if( $this->columns == 0 )

14     {

15       echo $command;

16       echo "<BR>";

17       echo pg_affected_rows( $this->result );

18       echo " row(s) affected";


20       if( pg_last_oid( $this->result ) != 0 )

21         echo ", OID =  ". pg_last_oid( $this->result );

22     }

23     else

24     {

25       $row_count     = pg_num_rows( $this->result );


27       $this->start_table( $command );


29       for( $row = 0; $row < $row_count; $row++ )

30         $this->append_row( $this->result, $row );

31     }

32   }

33 }

In this version, you check the result set column count at line 13. If you find that the result set contains 0 columns, echo the command text and the number of rows affected by the command. You also call the pg_last_oid() function. pg_last_oid() returns the OID (object ID) of the most recently inserted row. pg_last_oid() returns 0 if the command was not an INSERT or if more than one row was inserted.

The final results are shown in Figure 15.18.

Figure 15.18. Executing an INSERT command?final result.


    Part II: Programming with PostgreSQL