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> 2 3 <!-- Filename: client4.html> 4 5 <HEAD> 6 <TITLE>client4a</TITLE> 7 <BODY> 8 <CENTER> 9 <FORM ACTION="client4a.php" METHOD="POST"> 10 <I>Enter SQL command:</I><br> 11 12 <INPUT TYPE="text" 13 NAME="query" 14 SIZE="80" 15 ALIGN="left" 16 VALUE=""> 17 18 <BR><BR> 19 <INPUT TYPE="submit" VALUE="Execute command"> 20 </FORM> 21 </CENTER></BODY> 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.
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
6
7 # Filename: client4a.php
8
9 include( "secure/my_connect_pg.php" );
10 include( "my_table_e.php" );
11
12 $command_text = $HTTP_POST_VARS[ "query" ];
13
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" );
21
22 $table = new my_table( $db_handle, $command_text );
23 $table->finish();
24
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, 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, client4a retrieves 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 posted values into a single associative array named $HTTP_POST__VARS[] (also known as $_POST starting in PHP version 5). 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 can be a little nicer to your users by intercepting empty commands at lines 14 through 16 and displaying a less intimidating error message.
The remainder of this script is straightforward: simply 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).
That worked nicely. Let's try another query (see Figures 15.12 and 15.13).
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 );
4
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 );
13
14 $this->start_table( $command );
15
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, we suppress error reporting by prefixing the call to pg_query() with an @. At line 5, 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 can build an HTML table from the result set as before.
Now, if you run into an error condition, the result is far more palatable (see Figures 15.14 and 15.15).
Notice that you see only one error message this time. In Figure 15.13, you saw multiple error messages. Not only had client4a failed to intercept the original error, but it went on to use an invalid result set handle; when you fix the first problem, the other error messages 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.
After clicking on the Execute Command button, you see the result displayed in Figure 15.17.
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, in Listing 15.19, 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 );
4
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
13 if( $this->columns == 0 )
14 {
15 echo $command;
16 echo "<BR>";
17 echo pg_affected_rows( $this->result );
18 echo " row(s) affected";
19
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 );
26
27 $this->start_table( $command );
28
29 for( $row = 0; $row < $row_count; $row++ )
30 $this->append_row( $this->result, $row );
31 }
32 }
33 }
|
This version checks 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 (that gives you feedback similar to what you would see using the psql client). You can 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.
Now that you know how to write an interactive query processor using PHP, don't forget the security implications of doing so. Make sure that you connect to the PostgreSQL server using an account with very few privileges: If you give a visitor lots of privileges and a way to execute any command he wants, you're inviting disaster.
More typically, you ask a visitor to your site to type in a value that you use to construct an SQL command. For example, you may prompt the user for a “Customer Name” and retrieve the matching record with a query such as
$query = "SELECT * FROM customers WHERE customer_name = '"; $query .= $_POST['customer_name'] . "'";
That works, sometimes. What happens if the user types in a value such as “Freddy's Fudge Factory”? Then your query becomes
SELECT * FROM customers WHERE customer_name = 'Freddy's Fudge Factory'.
See the problem? As far as PostgreSQL is concerned, a string literal is enclosed in a pair of single quotes. The PostgreSQL parser thinks the customer name ends at Freddy—the rest of the customer name is just a syntax error. To fix that sort of problem, just quote any string values with the pg_escape_string() function, like this:
$customer_name = pg_escape_string( $_POST['customer_name'] ); $query = "SELECT * FROM customers WHERE customer_name = '$customer_name'";