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.
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
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.
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
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
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
Here's an explanation of how this works:
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.
You use responseText to get the data. A hidden
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.
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.
220.127.116.11. 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.
18.104.22.168. Processing results
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].
Joins, Unions, and Views
Storing Small Amounts of Data
Locking and Performance
Users and Administration