How to securely store data into MySQL using the Stanford Web Application Toolkit

From Web Services Wiki

Jump to: navigation, search

Contents

Problem

You want to store data in a MySQL database securely, preventing SQL injection.

Solution

Undo magic quotes (automatic string escaping)

Read about magic quotes, why they are bad, and how to remove them.

Here is one method of removing magic quotes:

// Include StanfordUtil
include_once("stanford.util.php");
 
// Undo magic quotes
StanfordUtil::undo_magic_quotes();

Manually escape all strings that go into SQL queries

In order to store data into MySQL securely, it must be escaped. Database queries are susceptible to injection attacks whereby the attacker creates a query of his or her own with malicious intent. Escaping form and other external input ensures that the query will run as intended. Never construct database queries without escaping user-inputted data.

Escape SQL data using the MySQLi/StanfordDatabase function escape_string.

// Escape data
$string = $db->escape_string($string);
 
// Place escaped string in MySQL query
$sql = "SELECT first_name FROM contacts WHERE last_name = '$string'";

Example

This example assumes that there is a connection to a MySQL database already established and stored in a variable called $db. The script reads in two parameters from a form, name and comment, and saves them in the database.

Writing a secure SQL statement using form input

// Include StanfordUtil
include_once("stanford.util.php");
 
// Check if the form was submitted
if($_REQUEST['submit'] == true) {
 
  // IMPORTANT: Always undo magic quotes before manually escaping strings
  StanfordUtil::undo_magic_quotes();
 
  // IMPORTANT: Manually escape form input before writing SQL statement
  $name    = $db->escape_string($_REQUEST['name']);
  $comment = $db->escape_string($_REQUEST['comment']);
 
  // Create a query
  $sql = "INSERT INTO comments (name, comment) VALUES ('$name', '$comment')";
 
  // Run the query
  $result = $db->query($sql);
}

Discussion

Why do I have to escape data that goes in a SQL statement?

The following is an example of an SQL statement constructed without escaping input.

$var = "It's a string";
$sql = "SELECT * FROM table WHERE sentence = '$var'";

The SQL statement in this example becomes SELECT * FROM table WHERE sentence = 'It's a string', which cannot be interpreted correctly because of the apostrophe in "It's." In this case, an error is raised.

The following code is an example of an SQL injection attack.

// In most cases, $username would be set via a form
// For ease of illustration, its value is hard-coded here
$username = "blah'; DELETE FROM posts WHERE author = 'admin";
 
// SQL query
$sql = "SELECT * FROM posts WHERE author = '$username'";

Here, the SQL statement turns into SELECT * FROM posts WHERE author = 'blah'; DELETE FROM posts WHERE author = 'admin', which is clearly not what we want to happen.

Escaping input tells the SQL interpreter how to differentiate between data and query language by adding slashes before control characters such as quotes. Always escape input so that malicious users cannot modify SQL statements within your application.

References

Personal tools