Creating a user with SELECT privileges only on a specific database, Part 3

Part 3 of 3. This part covers using Dreamhost‘s web panel for creating a user with SELECT privileges only for use while displaying data on a webpage. See Protecting Your Data from an SQL Injection Attack for more. Part 1 covered the actual SQL commands. Part 2 covered using phpMyAdmin.

Using Dreamhost‘s interface.

I picked Dreamhost because they host conlang.org and most of the subdomains of conlang.org. Also, they disallow using the methods in part 1 and part 2.

After logging in to Dreamhost‘s panel, click on Databases under Let’s Get Started!

Let's Get Started screenshot

Under the list of databases, click Add a New User on the right.

List of databases screenshot

Select “Create a new user now…” from the select menu, then fill in the username and password. Click Add new user now! Note that the instructions say: “This will grant a user full access to your “database_name” database. To limit this user’s database access privileges to “terjemar_newdict”, simply click their username on the screen that follows. ”

adding a new user screenshot

Now we get our success message: “User “selectonly_user” has been granted access to your “terjemar_newdict” database! If you’d like to fine-tune their permissions or set where they can connect from, do so from here.” You can also click on the username listed by the database.

selecting the user for editing screenshot

Make sure only Select is checked. The other options are Insert, Update, Delete, Create, Drop, Index, and Alter. The first three allow the use of those statements, so INSERT statements, UPDATE statements, and DELETE statements. Create allows for CREATE TABLE statements, Drop for DROP TABLE statements, Index for CREATE INDEX and DROP INDEX statements, and Alter for ALTER TABLE statements.

modifying privileges screenshot

Click on “Modify selectonly_user now!”.

final success message

And we have our success statement.

Creating a user with SELECT privileges only on a specific database, Part 2

Part 2 of 3. This part covers using phpMyAdmin for creating a user with SELECT privileges only for use while displaying data on a webpage. See Protecting Your Data from an SQL Injection Attack for more. Part 1 covered the actual SQL commands. Part 3 will cover using Dreamhost‘s web panel. I picked Dreamhost because they host conlang.org and most of the subdomains of conlang.org. Also, they disallow using the methods in part 1 and part 2.

Using phpMyAdmin:

From the homepage, go to the Privileges tab.

phpMyAdmin homepage

There you will find an “add a new user” link.

add a new user link screenshot

Click that for the Add a New User form.

add a new user form screenshot

Fill this out. Make sure None is the option under Database for user, and do not check anything under Global privileges. Click GO.

success screenshot

This display the green success checkmark and gives the SQL for what has been done:

CREATE USER 'username'@'%' IDENTIFIED BY '***';
GRANT USAGE ON *.* TO  'username'@'%' IDENTIFIED BY '***' ...;

GRANT USAGE does not grant anything at all.

Underneath is the Edit User form (also reachable through the list of users).

Edit user form screenshot

Note that the first part is labelled Global privileges. We don’t want those, we want privileges for a specific database (‘test’). That is under the Global Privileges part. We select the database from the select box and get to the database’s privileges page:

database specific edit form screenshot

Here we check the box marked SELECT and click GO.

final success screenshot

And Success!. The SQL here reads:

GRANT SELECT ON test.* TO 'username'@'%';

Creating a user with SELECT privileges only on a specific database, Part 1

Part 1 of 3. This part covers the actual SQL commands for creating a user with SELECT privileges only for use while displaying data on a webpage. See Protecting Your Data from an SQL Injection Attack for more. Part 2 will cover using phpMyAdmin. Part 3 will cover using Dreamhost‘s web panel. I picked Dreamhost because they host conlang.org and most of the subdomains of conlang.org. Also, they disallow using the methods in part 1 and part 2.

Using SQL:

First, you have to have GRANT privileges yourself to do this. If so, use the CREATE USER statement, like so:

CREATE USER 'username'@'hostname';

The above creates a user without a password. (Not a good idea.)

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

This creates the user and the password. The main issue with this syntax is that the password is right there in plain text. There is a MySQL function which creates an encoded password. That is the PASSWORD() function. Like so:

SELECT PASSWORD('badpwd');

returns ‘*AAB3E285149C0135D51A520E1940DD3263DC008C’. You can then use that encoded string in the CREATE USER statement:

CREATE USER 'username'@'hostname' IDENTIFIED BY 
PASSWORD '*AAB3E285149C0135D51A520E1940DD3263DC008C';

Make sure to include that PASSWORD after IDENTIFIED BY.  You can also use the ‘%’ wildcard for the hostname so that the given user can log on from any host:

CREATE USER 'username'@'%' IDENTIFIED BY 
PASSWORD '*AAB3E285149C0135D51A520E1940DD3263DC008C';

Specifying privileges is done using a GRANT statement:

GRANT SELECT ON database_name.table_name TO 'username'@'%';

This allows the user access only to the specified database and table and only for use of SELECT statements.

GRANT SELECT ON database_name.* TO 'username'@'%';

allows usage on all tables in the named database.

So, two statements will do it:

CREATE USER 'username'@'%' IDENTIFIED BY 
PASSWORD '*AAB3E285149C0135D51A520E1940DD3263DC008C';
GRANT SELECT ON database_name.* TO 'username'@'%';

Using mysqli and parameterized statements

mysqli is the PHP extension that is recommended for use with current versions (4.1.3 and later) of MySQL. The way to connect to a MySQL database using mysqli is:

$host = "my_host"; //"localhost" or "http://mysql.host.com"
$user = "my_username"; //an authorized user of the MySQL database
$password = "my_password"; //my_username's password
$database = "my_database"; //the database we want to use.

$mysqli = new mysqli($host, $user, $password, $database);

if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}

Keep note of that $mysqli variable. It gets used a lot.

Querying the database can be as simple as:

$mysqli->query("some valid SQL statement");

This will return TRUE if the SQL statement was successful. So, if you are not expecting any data to be returned from the server, this bit of code will work fine:

if ($mysqli->query("some valid SQL statement") === TRUE) {
    //do stuff, or not, like print a success statement
}
$mysqli->close();

If you are using a SELECT statement to get data from the server, then you can do so:

if ($result = $mysqli->query("some valid SQL statement")) {
    //do stuff with the data
    while ($row = $result->fetch_row()) {
        //do stuff with the data in the $row array
    }
    $result->close();
}
$mysqli->close();

In addition to fetch_row(), there is also fetch_array() and fetch_assoc(). The full list of $result functions is here.

But the true wonderfulness of mysqli comes from the parameterized queries.

$stmt = $mysqli->stmt_init();
if ($stmt->prepare("some valid SQL query")) {
    $stmt->bind_params("s...", $vars);
    $stmt->execute();
    $stmt->bind_result($vars);
    while ($stmt->fetch()) {
        //do stuff with the data
    }
    $stmt->close();
}
$mysqli->close();

Yeah, right, you say. It looks complicated, but really, it isn’t. And some parts are optional.

I mentioned earlier that parameterized queries were one line of defense against sql injection attacks because they force the database to interpret anything within the bound variable as data and not as SQL instructions. So, here is how that works.

First, we initialize the statement with $stmt = $mysqli->stmt_init();. This step is optional, but it is good practice. By initializing, the next function called on $stmt has to be $stmt->prepare().

$stmt->prepare() takes a query (either directly or as a variable) and prepares it for the server. If the query requires data input, such as in a WHERE clause or in an INSERT statement, then one uses ‘?’ markers as placeholders for the data. Like so:

SELECT FirstName, LastName FROM Friends WHERE FavoriteColor = ?;

In PHP:

$stmt = $mysqli->stmt_init();
if ($stmt->prepare("SELECT FirstName, LastName
    FROM Friends WHERE FavoriteColor = ?")) {
    ...

So far, so good. Make sure not to include the normal final semi-colon in the SQL statement.

The next step is to bind the parameters, or tell the database what value the ? has. This is done using $stmt->bind_param(); This function takes a minimum of two arguments. The first is a string with information on the type of data. The second is the variable that holds the data. The information string can consist of the following characters only: b, d, i, and s. These stand for blob, double, integer, and string. Since FavoriteColor is a word, we use ‘s’ to identify the input as a string. The number and sequence of characters in the string must correspond to the number and sequence of ?s in the query. The number and sequence of the data- holding variables must correspond to the same number and sequence.

$color = "purple";
$stmt = $mysqli->stmt_init();
if ($stmt->prepare("SELECT FirstName, LastName
    FROM Friends WHERE FavoriteColor = ?")) {
       $stmt->bind_param("s", $color);
       ...

OK so far? Want another example? How about:

INSERT INTO Friends (FirstName, LastName, FavoriteColor)
     VALUES (?, ?, ?);
//the data
$firstname = "David";
$lastname = "Peterson";
$color = "orange";

$stmt = $mysqli->stmt_init();
if ($stmt->prepare("INSERT INTO Friends (FirstName, LastName,
    FavoriteColor) VALUES (?, ?, ?)")) {
       $stmt->bind_param("sss", $firstname, $lastname, $color);
       ...

See, three ?s, three characters in the first argument, and three variables with our data.

Then we execute the statement, using $stmt->execute(), which sends our query to the database.

$color = "purple";
$stmt = $mysqli->stmt_init();
if ($stmt->prepare("SELECT FirstName, LastName FROM Friends
    WHERE FavoriteColor = ?")) {
       $stmt->bind_param("s", $color);
       $stmt->execute();
       ...

Since this SELECT statement is returning two values (FirstName and LastName), we can also bind the results. This part is optional, though still a good idea.

$color = "purple";
$stmt = $mysqli->stmt_init();
if ($stmt->prepare("SELECT FirstName, LastName FROM Friends
    WHERE FavoriteColor = ?")) {
       $stmt->bind_param("s", $color);
       $stmt->execute();
       $stmt->bind_result($firstname, $lastname);
       ...

After binding the results, we can use $stmt->fetch() to get the data from the result set. All that’s left to do is close the statement with $stmt->close(), freeing up resources, and close the connection with $mysqli->close().

$color = "purple";
$stmt = $mysqli->stmt_init();
if ($stmt->prepare("SELECT FirstName, LastName FROM Friends
    WHERE FavoriteColor = ?")) {
       $stmt->bind_param("s", $color);
       $stmt->execute();
       $stmt->bind_result($firstname, $lastname);
       while ($stmt->fetch()) {
           //do stuff with the data
           echo "$firstname $lastname";
       }
       $stmt->close();
}
$mysqli->close();

The other example:

//the data
$firstname = "David";
$lastname = "Peterson";
$color = "orange";

$stmt = $mysqli->stmt_init();
if ($stmt->prepare("INSERT INTO Friends (FirstName, LastName,
    FavoriteColor) VALUES (?, ?, ?)")) {
       $stmt->bind_param("sss", $firstname, $lastname, $color);
       $stmt->execute();
       $stmt->close();
}
$mysqli->close();

INSERT does not return any results for binding. If you want information regarding the success of the query, you can use $mysqli->affected_rows to get back a count of affected rows. Even more useful is $mysqli->insert_id which returns the auto-incremented ID for the new row (if there is one, and if you call it right away before inserting or updating anything else.)

If you chose to skip binding the results, then you cannot use $stmt->fetch(). Instead, you must use  $stmt->get_result() and then $result->fetch_row() or an equivalent. Like so:

$color = "purple";
$stmt = $mysqli->stmt_init();
if ($stmt->prepare("SELECT FirstName, LastName FROM Friends
    WHERE FavoriteColor = ?")) {
       $stmt->bind_param("s", $color);
       $stmt->execute();
       $result = $stmt->get_result();
       while ($row = $result->fetch_row()) {
           //do stuff with the data in the $row array
       }
       $result->close();
}
$mysqli->close();

And that’s how you use mysqli and parameterized statements.

Oct 19, 2011: Updated to fix typos in the method name.
Apr 19, 2014: Updated to add missing commas in the code.

Protecting Your Data from an SQL Injection Attack

Introduction

Generally speaking, SQL injection is an attack on a website via a database attached to the site. This database might contain site content or it might contain user login and password information. A successful attack would allow the attacker complete access to the database, able to add, change, delete, and download the database content.

So how does the attack work? It works by using quote characters to end an input string prematurely, and then adding SQL statements to the input.

xkcd #327 “Exploits of a Mom” has an example of an SQL injection attack with a child named “Robert’); DROP TABLE Students; –”. The “’);” after “Robert” is a typical end to the SQL insert statement. It is followed by another SQL statement “DROP TABLE Students;” And the final “–” starts an SQL comment, thus guaranteeing that whatever the original statement was, the rest of it would be commented out.

In more detail: presumably the school has a database with a table called Students. In the normal course of things, someone would enter “Robert” into the first_name field, and Robert’s last name (let’s say it’s “Smith”) into the last_name field, and so on. The SQL statement that would be sent to the database would look like this:

INSERT INTO Students (first_name, last_name) VALUES (‘Robert’, ‘Smith’);

And one row would be inserted into the Students table. However, if instead of “Robert”, someone entered “Robert’); DROP TABLE Students; –” into the first_name field, then the following would be sent to the database:

INSERT INTO Students (first_name, last_name) VALUES (‘Robert’); DROP TABLE Students; --’, ‘Smith’);

One row would be inserted into the Students table (though last_name would be blank), and then the table Students would be deleted from the database. The last bit –’, ‘Smith’); would be interpreted as a comment and so ignored. See SQL Injection Attacks by Example for a more detailed examination of how this attack works.

The database is vulnerable anytime a user interacts with it. However, there are ways to defend against such an attack. I will discuss three of these ways:

  1. sanitize inputs
  2. use parameterized queries
  3. restrict permissions

None of these strategies is a guaranteed, will-stop-100%-of-attacks method. However, each can help lower the likelihood of a successful attack. So, these different defense strategies are not mutually exclusive, and it is best, whenever possible, to use all three.

Sanitize Inputs

The punchline of the xkcd comic has the mother saying “I hope you’ve learned to sanitize your database inputs.” One common way of sanitizing input is to escape the characters by adding another quote mark or a backslash to any quotes in the input. If the hapless school had escaped the input, then this is what would have resulted:

INSERT INTO Students (first_name, last_name) VALUES (‘Robert\’); DROP TABLE Students; --’, ‘Smith’);

The student would have a first name of “Robert’); DROP TABLE Students; –” and a last name of “Smith” and no tables would have been deleted. By escaping the single-quote with a backslash, the database interpreted it as part of the input. Characters that ought to be routinely escaped are: single quotes, double quotes, backslashes, and the NUL character. Control characters like CTRL-Z also ought to be escaped as they, too, can interfere with the interpretation of input strings.

Since this blog deals primarily with PHP, I will discuss several PHP functions that can assist.

  1. addslashes(): This function adds a backslash to single quotes, double quotes, backslashes, and NUL automatically.
  2. mysql_real_escape_string(): This is a MySQL-specific PHP function that escapes nulls, newlines, carriage returns, substitute characters, double quotes, single quotes, and backslashes. The  mysql_escape_string() function is deprecated, and should not be used, as it disregards the character set the string is in. The mysqli (mysql-improved) equivalent of mysql_real_escape_string() is mysqli::real_escape_string().

The following commands help to keep any html tags from getting into the database. Thus, if you end up displaying this data, it won’t change the html markup or add a javascript function to your page. This also helps to prevent someone from inserting a malicious script into your database.

  1. htmlspecialchars(): transforms ampersands, double quotes, less than and greater than into html entities. If the ENT_QUOTES flag is set, it also transforms single quotes. The htmlentities() function does the same thing, also encoding most Latin-1 non-ascii characters.
  2. str_replace(): can be used to replace any characters, like so:
    $string = "{user_input}";
    $bad = array('&', '"', "'", '<', '>', '\\', '%', '_');
    $good = array('&amp;', '&quot;', '&#039;', '&lt;', '&gt;', '&#092;', '&#037;', '&#095;');
    $sanitized = str_replace($bad, $good, $string);

    The backslash in $bad needs to be escaped in order to be recognized as a regular character.
  3. strip_tags() can be useful, too, as it strips the input of any html and php tags that are found.

Use parameterized queries

Parameterized queries are fun, convenient, faster, and much, much safer than merely escaping a string. Parameterized queries take an SQL statement like INSERT INTO Students (first_name, last_name) VALUES (?, ?); and then bind or assign the inputted values into the query, in essence escaping it for you. This is preferable to escaping the string yourself because it is less error-prone and you can’t forget to do it.

The mysql extension for PHP does not support parameterized queries. However, the mysqli (the ‘i’ stands for “improved”) extension (recommended for MySQL v4.1.3 and later) and the PDO extension do support parameterized queries. If you are not sure which to use, see this overview. I am partial to PDO, but mysqli is a good extension as well. I will write a future post on migrating your PHP code from mysql to mysqli.

Restrict permissions

If you are using your database to provide site content only (that is, you have no registered users), then it is a fairly simple thing to make your database read-only. That is, create a user for your database that has select permissions only and cannot insert, update, or delete any content. Use this account to connect the website with the database. That way anyone who manages to get around the escaped string in a parameterized query method cannot add, change, or delete your data through the website, accidentally or otherwise.

Of course, this is more complicated if you do have registered users. Make sure that any statements that deal with input from a user that is not logged in use a restricted, select-only account. Also, if you have your own administrative interface (a form where you enter stuff into your database rather than using phpMyAdmin or some other application), that page will need to access the database using a different account.