Hack 100. Run SQL from a Web Page

A web-based interface to your SQL machine can be useful. Fortunately, plenty of options are available to you.

You can connect to your SQL database in a number of ways. You can use the command-line interface as shown in [Hack #1], and you can execute SQL from a programming language as shown in [Hack #2]. Another option is to work from a web browser. Either each vendor has its own mechanism for this, or a third-party product is available.

12.5.1. MySQL

phpMyAdmin (http://www.phpmyadmin.net), shown in Figure 12-1, is a tool that allows MySQL administration over the Web. It is popular with web hosting companies because it allows their clients to control MySQL accounts without requiring shell access.

Figure 12-1. The phpMyAdmin user interface

The phpMyAdmin tool set includes step-by-step forms for most of the commonly used facilities of SQL. Creating a table is relatively intuitive; setting permissions is a breeze. After you click the Go button, it shows you the SQL that has been generated so that you can easily find the exact syntax for those obscure SQL commands that you rarely use. But you also have the opportunity to execute arbitrary SQL if you find the interface inadequate.

12.5.2. SQL Server

For SQL Server, you can use the Web Data Administrator utility available from Microsoft, and shown in Figure 12-2.

Figure 12-2. Microsoft Web Data Administrator

You can also use WebSQL Console, available from http://www.websqlconsole.com, and shown in Figure 12-3.

Figure 12-3. WebSQL console

 

12.5.3. Oracle

Oracle provides a web interface: iSQL*Plus, which is shown in Figure 12-4.

Figure 12-4. iSQL*Plus

By default, the iSQL*Plus interface is available at http://localhost:5500/em/console/logon/logon for administration and at http://localhost:5560/isqlplus/dynamic for general SQL access.

12.5.4. PostgreSQL

The program phpPgAdmin, available at http://www.phppgadmin.org, allows you to run queries from a web page, and it provides access to other administrative functions. It is shown in Figure 12-5.

Figure 12-5. phpPgAdmin

 

12.5.5. Hacking the Hack

You can exploit an SQL web-based interface and use it as the server-side script for your AJAX applications. An AJAX page usually features a client-side page that runs in a browser and a server-side script that is invoked from the client. In an AJAX application, you use JavaScript to send queries to the server and then make changes to the current page using JavaScript DOM methods.

The example program shown in Figure 12-6 runs inside a browser; the code that follows is a plain HTML page with some embedded JavaScript. The only server-side script needed is phpMyAdmin.

The user can specify the name of a country, his SQL username, and his password. The client-side script formulates an SQL query to get the population for that country. It sends the query to the general-purpose SQL web interface and then displays the result without refreshing the whole page.

Figure 12-6. AJAX demonstration

You can try this application at http://sqlzoo.net/ajax.htm. The user name is scott; the password is tiger.

The code for this example is a static HTML page that does not need to be interpreted at the web server; all the processing is done on the client:

SQL Hacks AJAX Demo

country :
user___ _:
password:

---

Population:

 

Here's an explanation of how this works:

 

Line 4

The SQL statement is assembled using JavaScript string functions. Note that the escaping of the quote does not protect this application from an SQL injection attack, but it does prevent an SQL syntax error when querying the country Cote d'Ivoire.

 

Line 7

phpMyAdmin encodes the SQL and other parameters as CGI get variables, so the URL contains each value required. It is also possible to perform POST requests from an AJAX application.

 

Line 24

You use responseText to get the data. A hidden

is set to the response from phpMyAdmin. Although the user cannot see the response, the browser parses it and makes it available to DOM methods in JavaScript.

 

Line 33

The function show(i) extracts the TD element i from the table with the ID table_results. The entire result set is stored in this array; if the SQL statement resulted in more than one value, the other results would also be available.

Your web page must be on the same server as the phpMyAdmin tool. This is because cross-server access is disallowed by default on most browsers. If you need to access data on a different server, you can set up a proxy so that a request is made to the actual server via the script's server. If the Apache RewriteEngine is available to you, the following instructions in the .htaccess file will do the trick. With this you can make it appear that the phpMyAdmin script http://foo.bar/sql.php is on your own machine:

# .htaccess for ApacheRewriteEngine on# This is a proxy redirectRewriteRule sql.php(.*) http://foo.bar/sql.php$1 [P]

 

12.5.6. Using Other Web Interfaces

You can use a similar technique for the Oracle, PostgreSQL, and SQL Server web interfaces; however, a little investigation is required.

12.5.6.1. Authentication

You need to know how the authentication is managed. In phpMyAdmin the authentication is via HTTP and the JavaScript open command includes parameters for the username and password. In Oracle's iSQL the authentication is managed by a cookie. It is possible to obtain the cookie by making requests from JavaScript.

12.5.6.2. CGI parameters

You need to know the name of the CGI parameters. In phpMyAdmin the SQL statement is in sql_query, and for Oracle's iSQL the SQL is in the CGI parameter script.

12.5.6.3. Processing results

You need to know the structure of the results page in order to show the values required. For phpMyAdmin we have chosen to fetch the page intended for printing. This has a simple structure, and it's easy to probe the results of the query using JavaScript DOM functions such as getElementById and getElementsByTagName.

12.5.7. Security

This technique relies on the user having access to an SQL account on the database server. You might safely use an anonymous account as long as you take the precautions outlined in "Allow an Anonymous Account" [Hack #97].