Archive for the ‘MySQL’ Category

More Thoughts on SQL Injection Attacks

Friday, May 13th, 2016

As I discussed in an earlier post, my sites are frequently attacked by malicious actors trying to get into my database by using SQL injection techniques. I stopped most of them by doing input validation and if the input fails validation, returning a 404 not found. I didn’t do this on all of my pages so from time to time they find a page that does”t fail with bad input and send thousands of page requests to it.

I recently updated my sites and took the opportunity to familiarize myself with the latest best practices for mitigating attacks. I added a line to my database initialization script, sanitize all inputs, and use either prepared statements or queries that use whitelisted terms. Here’s my current initialization script.


<?php

$user = 'mysql_user';
$pass = 'supersecurepasswors';
$host = 'localhost';
$db_name = 'website_database';

try {
    $dbWG = new PDO("mysql:host=$host;db_name=$dbname", $user, $pass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") );
    $dbWG->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $dbWG->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $dbWG->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}
catch(PDOException $e) {
    $e->getMessage();

The only way I found to get attacks to stop is to return a 404 not found message to the bot. After a few tries they usually give up. Otherwise they will try thousands of times.

So first I sanitize the input. Here’s a simple one where the input has to be a digit:


$number_of_letters = (isset($_GET['n']) ? $_GET['n'] : 1);
if ( !is_numeric($number_of_letters) ) {
    include_once('dieInAFire.inc');
}

I have a $showError variable in my header that lets me turn off and on information on attacks. I usually leave it on and peruse the error log to make sure legitimate requests aren’t accidentally blocked. I wrote this include file to use after sanitizing inputs fails.


<?php
$url = isset($_SERVER['REQUEST_URI'])  ? $_SERVER['REQUEST_URI']  : '';
$ref = isset($_SERVER['HTTP_REFERER']) ? $_SERVER['HTTP_REFERER'] : '';
$ip  = isset($_SERVER['REMOTE_ADDR'])  ? $_SERVER['REMOTE_ADDR']  : '';

if ($showError == TRUE) {
    error_log("Long string in $calledFileName: URL is $url and IP is $ip & ref is $ref");
}
header("HTTP/1.0 404 Not Found");
die();
?>

Note that this has to be called before the statement.

If it is a legitimate request, I make then use a prepared statement to execute it.


$qry.=  "HAVING COUNT(sorted_letters) > :number_of_letters ";

$stmt = $dbWG->prepare($qry);
$stmt->bindParam(':number_of_letters', $number_of_letters);
$stmt->execute();

Getting one row from a MySQL query

Thursday, April 28th, 2016

Often you just need one value or one row of values from a table. If your query returns a unique row because of the query or because you limited it to one row, then you don’t need to go through the foreach loop to get the values. You can change the fetchAll() to fetch() and use just one line to get the value.


// get the current category name
$qry  = "SELECT name FROM `website`.`book_category` ";
$qry .= "WHERE id = :categoryID";

$stmt = $dbWG->prepare($qry);
$stmt->bindParam(':categoryID', $categoryID);
$stmt->execute();

$results = $stmt->fetch();
$categoryName = $results['name'];

A Quick Note on Migrating to PDO

Tuesday, April 12th, 2016

Migrating from mySQLi to PDO was really painless. I changed the database access as described previously. Then using the same query as previously, I added the database name `website` and then used the following code:


$qry = "SELECT * FROM product;
$res= $dbWG->query($qry);

if (!$res)
    die(mysqli_error($dbWG));

$numRows = mysqli_num_rows($res);

for ($i = 0; $i < $numRows; $i++) {
    $row   = $res->fetch_array();


$qry = "SELECT * FROM `website`.`product`;
$stmt = $dbWG->prepare($qry);
$stmt->execute();

$results = $stmt->fetchAll();
foreach($results as $row) {

One of the reasons to use PDO is that it has prepared statements that will automatically escape inputs so that injection attacks are mitigated.

It is simple to change the your code if you are using input from the user to construct the query.
If you do a simple convert, like above, to you end up with something like this:


// get the current category name
$qry  = "SELECT name FROM `website`.`book_category` ";
$qry .= "WHERE id = $categoryID";
  
$stmt = $dbWG->prepare($qry);
$stmt->execute();

$results = $stmt->fetch();
categoryName = $results['name'];

A simple change in the query and a bind statement lets PHP automatically escape the user input.


// get the current category name
$qry  = "SELECT name FROM `website`.`book_category` ";
$qry .= "WHERE id = :categoryID";

$stmt = $dbWG->prepare($qry);
$stmt->bindParam(':categoryID', $categoryID);
$stmt->execute();

$results = $stmt->fetch();
$categoryName = $results['name'];

Using the PDO MySQL extension

Sunday, April 3rd, 2016

As I mentioned in previous posts, my server is continuously being attacked by hackers attempting to exploit MySQL injection vulnerabilities. I use input validation and mysql_real_escape_string to keep them from getting into the databases. Another way to prevent injection is to use stored procedures. Both PHP and perl allow stored procedures. To use them with PHP you use the PHP Data Objects extension. It should be automatically installed with your PHP and MySQL installation.

To test if PDO is enabled, at the command line type:


php -i|grep PDO

You should get something like this.

PDO
PDO support => enabled
PDO drivers => mysql, sqlite
PDO Driver for MySQL => enabled
PDO Driver for SQLite 3.x => enabled

As I mentioned in a previous post, I like to put the database setup info in a separate directory outside of the document root and then include it in the php file where it is used. The file would look like this:


<?php
$user = 'username';
$pass = 'password';
$host = 'localhost';
$db_name = 'website';

try {
    $dbWG = new PDO("mysql:host=$host;db_name=$dbname", $user, $pass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") );
    $dbWG->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $dbWG->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $dbWG->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}
catch(PDOException $e) {
    $e->getMessage();
    file_put_contents('/var/log/PDO_WG_errors.log', $e->getMessage(), FILE_APPEND);
}
?>

Don’t forget to use start and end with the php markers or the code will show up on your page. To keep the naming convention consistent with what I have been using, this file would be called wg_db_pdo.inc, since the original PEAR version of the files was called wg.db.inc, and the MySQL versions have been called wg.db.my.inc.

I used try-catch pattern so that I could find out if errors were happening while debugging, but it also is useful in production. Writing the errors to a file also prevents hackers from seeing errors on your site and giving them ideas about how to break in. To see if it was working, I changed the password to an invalid password and got this in the error log, so I know it works.


SQLSTATE[28000] [1045] Access denied for user 'username'@'localhost' (using password: YES)

I was getting this error in my normal php error logs.
Invalid catalog name: 1046 No database selected
until I realized that the full database name is required when accessing a table.


SELECT * FROM `database`.`table`

not

SELECT * FROM `table`

When setting up the database access there are three attributes that I generally want on the database. There are several ways that the data can be returned. I usually want it to be in an associative array so I set the ATTR_DEFAULT_FETCH_MODE to FETCH_ASSOC. The data is in an associative array indexed by column name. As shown in the sample code below, it is straightforward to access the data with this method. Sometimes it is useful to have the data indexed by column number and you can override the default when you make your query. All of the options are listed on php.net.

I’ve been exploring various options and the code might be useful to someone, so I’ve included it below.


<!DOCTYPE html>
<html lang="en">

<head>
  <title>PDO Test</title>
</head>
<body>
<?php
echo "Available drivers: ";
print_r(PDO::getAvailableDrivers());

require_once('wg_db_pdo.inc');

    // Don't use this construct. It works but gives an error on the next query
    // 2014 Cannot execute queries while other unbuffered queries are active.
    //$status = $dbWG->exec('SELECT * FROM `database`.`product`');
    //echo "The result of the select statement was $status";

// Check to see if the MySQL driver is available
// http://php.net/manual/en/ref.pdo-mysql.php
if ($dbWG->getAttribute(PDO::ATTR_DRIVER_NAME) == 'mysql') {
    $stmt = $dbWG->prepare('SELECT * FROM `database`.`product` LIMIT 10',
        array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
    $stmt->execute();
    echo "<p>Connected to database</p>\n";

} else {
    die("Failed to open database");
}

# using the shortcut ->query() method here since there are no variable
# values in the select statement.
$result = $dbWG->query('SELECT name, id, tagline FROM `database`.`product` LIMIT 10');

echo "<h3>Read from the  result</h3>";
while($row = $result->fetch()) {
    echo $row['id'] . "   ";
    echo $row['name'] . "&mdash;   ";
    echo $row['tagline'] . "<br />";
}

// Resetting the cursor position is not supported.
echo "<h3>Reading from the same result yields nothing</h3>";
echo "<p>";
while($row = $result->fetch()) {
    echo $row['id'] . "   ";
    echo $row['name'];
    echo $row['tagline'] . "<br />";
}
echo "</p>";

//If you want to iterate twice over the results, fetch to an array and iterate over this array:
$stmt = $dbWG->prepare('SELECT * FROM `database`.`product` LIMIT 10');
$stmt->execute();

echo "<h3>You can get the row count in MySQL after the statement is executed</h3>";
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';

$results = $stmt->fetchAll();

echo "<h3>First iteration</h3>";
foreach($results as $row) {
    echo $row['name'] . "<br />";
}

echo "<h3>Second iteration</h3>";
foreach($results as $row) {
    echo $row['name'] . "<br />";
}

// If you load the results into an array, you can get the row count by counting on the array.
echo "<h3>Get the number of rows from array</h3>";
$numberOfRows = count($results);
echo "The number of rows is $numberOfRows";

// Alternatively, you need to do two queries. One to count and one for the data
echo "<h3>Get number of rows using count(*)</h3>";
$numRows = $dbWG->query('SELECT count(*) FROM `database`.`product` LIMIT 10')->fetchColumn();
echo $numRows;

echo "<h3>Fetch as number: FETCH_NUM</h3>";
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
  echo "$row[0] $row[1] <br />";
}

echo "<h3>Alternative Fetch as number: FETCH_NUM</h3>";
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_NUM);
 foreach($result as $key=>$val) {
    echo $key.' - '.$val[3].'<br />';
}

echo "<h3>Prepared Statement with Parameters</h3>";
$stmt = $dbWG->prepare("SELECT * FROM `database`.`product` WHERE id > :id AND name LIKE :name");

$product_id = 1;
$input_from_user = "Artic";
$name = "%" . $input_from_user . "%";
$stmt->bindParam(':id', $product_id, PDO::PARAM_INT);
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
// Alternatively
$stmt->bindValue(':name', "%{$input_from_user}%", PDO::PARAM_STR);

$stmt->execute();
$results = $stmt->fetchAll();

foreach($results as $row) {
    echo $row['name'] . "<br />";
}

// These examples use question mark placeholders
echo "<h3>Insert</h3>";
$stmt = $dbWG->prepare('INSERT INTO `database`.`product_log` (`product_id`) VALUES (?)');
for ($i = 0; $i < 10; $i++) {
    $product_id = $i + 10;
    $stmt->bindParam(1, $product_id);
    $stmt->execute();
}

// Insert with Array and placeholders
// The product_log has a source field called src
echo "<h3>Insert with Array and Placeholders</h3>";
$values = array('page1.php', 'page2.php', 'page3.php', 'page4.php');
$source = '';
$stmt = $dbWG->prepare('INSERT INTO `database`.`product_log` (`src`) VALUES (:source)');
$stmt->bindParam(':source', $source, PDO::PARAM_STR);
foreach($values as $source) {
    echo "The value to be inserted is $source<br />";
    $stmt->execute();
}

$insertId = $dbWG->lastInsertId();
echo "The ID of the last row is $insertId";

echo "<h3>Delete the last two rows</h3>";
$stmt = $dbWG->prepare('DELETE FROM `database`.`product_log` WHERE id >= ? AND id <= ?');
$min = $insertId - 1;
$max = $insertId;
$stmt->bindParam(1, $min, PDO::PARAM_INT);
$stmt->bindParam(2, $max, PDO::PARAM_INT);

$stmt->execute();
$count = $stmt->rowCount();
echo "The number of rows deleted is $count";

# close the connection
$dbWG = null;
?>
</body>

For further reading I suggest hashPHP to start and the PDO Book at php.net.

MySQL example: OUTER JOINs

Tuesday, April 7th, 2015

I often want to know which items in a table are not in another table. In this example, I have two tables of words. The big table has ~68,000 words and the small one has ~29,000. Since they both have the field name, I use an alias in the SELECT statement so that I don’t have to decipher the results. I also put the fields in the same order as the join so that the logic of the query is consistent with the SELECT statement, but you can put them in any order.

Remember that a LEFT OUTER JOIN contains all of the records from the first table. If there is no corresponding record in the second table, the values for its fields are null.


SELECT SmallTable.word AS SmallWord, BigTable.word AS BigWord
FROM SmallTable
LEFT OUTER JOIN BigTable 
ON  SmallTable.word = BigTable.word
WHERE BigTable.word IS NULL
ORDER BY `SmallTable`.`word` ASC

The query yields 364 words and looks like this:

LEFT OUTER JOIN Result

Just for fun, the reverse query yields 38,776 words and looks like this.


SELECT SmallTable.word AS SmallWord, BigTable.word AS BigWord
FROM BigTable
LEFT OUTER JOIN SmallTable 
ON SmallTable.word = BigTable.word 
WHERE SmallTable.word IS NULL
ORDER BY `BigTable`.`word` ASC

LEFT OUTER JOIN Result 2

Well Golly


Atheism Plus

Buy from Amazon