Chapter 6. Online Applications

An SQL database provides a powerful solution to many problems that arise in web-based applications. In addition to storing text and image data, you can use SQL to assist in building user interfaces. You can also use SQL to process your web logs, generate XML, and store the results of web scraping.

Despite these benefits, however, it can be dangerous to use SQL in online applications. This chapter includes advice on how to exploit an SQL injection vulnerability and how to avoid becoming a victim of such an attack.

Copy Web Pages into a Table

You can copy data from web pages into SQL using XSLT, which lets you pick and choose which parts of the web page to extract.

If the data in a web page is formatted consistently, you can write an XSLT stylesheet to convert it directly into SQL statements. You can target almost any kind of HTML web page, but it’s easier if the source is well-formed XML (such as XHTML) and has a simple structure. Wikipedia (http://wikipedia.org) is ideal. To demonstrate this technique, let’s start with the Wikipedia list of the top-grossing films worldwide (adjusted for inflation), shown in Figure 6-1.

Highest-grossing films according to Wikipedia
Figure 6-1. Highest-grossing films according to Wikipedia

After you turn this web page into some INSERT statements, you’ll be able to view the results with a SELECT statement:

1> SELECT * FROM film ORDER BY 2 DESC
2> GO title gross --------------------------------------------------- ...

Get SQL Hacks now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.