Excluding fields in the mongodb/mongodb library

Leave a comment

I am using the mongodb/mongodb library for a project of mine.  The API seems fairly different from the old PECL library and it also came with some other, albeit unexpected, baggage.

My understanding of the library is that it is intended to be a replacement for the existing PECL library and that it intends to strike a balance between the core library functionality while also giving it a more agile, for lack of a better term, release cycle.  What that means is that there is some basic MongoDB functionality written in C with the forward-facing API being written in PHP.

In and of itself this is not an issue… except for the one thing I was trying to do.  One of the practices I’ve heard about Mongo is to get Mongo to do as much as it can, but not to worry too much about complicated joins and such as you would in SQL.  In other words, don’t shy away from bringing data into the application to do some processing.

That was the practice I followed, which worked fine up until my data size started to increase.  Then I ran into an issue where I was seeing 50% of my response time being eating up by BSONDocument and BSONArray unserialization calls.  This was due to a sub-document I had in the document that could actually be quite large.

“Hmm,” I thought to myself.  “I’m sure glad I didn’t see this a month from now.”  I did some research and waffled a little between using MapReduce or using the newish aggregation features.  I opted for the aggregation feature, which is so incredibly powerful.  I see myself misusing this quite often.

But after changing the code to make it work with aggregation I still had the problem of the unserialization.  The documents are returned as a whole.  In the core functionality you can exclude individual fields by doing something like this:

1
2
3
4
db.inventory.find(
   { type: 'food', _id: 3 },
   { "classification.category": 0}
)

The second parameter (classification.category) tells Mongo to omit that field in the result set.  But I couldn’t find a way to mimic this behavior in the new library.  So I opened an issue on GitHub (when you run into a problem, contact the library maintainers or post on SO.  Don’t just leave it) asking how I might run this.

Jeremy Mikola responded with the solution.  There is a second option that you can put in that is documented, but I don’t think it is directly clear from the documentation exactly what it does.  Or, at least, the description isn’t very SEO friendly (Google-Based-Development FTW!).

How does that look?

Like this:

$find = [
    'customer_id' => $this->getId(),
    'invoked_test' => $testName
];
$options = [
   'sort' => ['created_at' => -1],
   'limit' => 10,
   'projection' => ['events' => 0]];

$currentResults = $tests->find($find, $options);

BOOM! 7.5 seconds off the response wall clock time.


The server requested authentication method unknown to the client [mysql_old_password]

2 Comments

I updated my server this morning, moving from Zend Server PHP 5.3 to 5.6 but when I did I got a really weird error.

The server requested authentication method unknown to the client [mysql_old_password]

I spent way too much time debugging it but it turns out that this is an incompatibility between MySQLnd and MySQL 5.5.  For the defaults, at least.  MySQLnd does not know about the mysql_old_password authentication method and so this error was being thrown.

The information I found said to update your user’s password and things should be fine.  I did, and they weren’t.  When I was updating the password it wasn’t using the new password hash for some reason.  So what I did was execute the following commands.

set session old_passwords=0;
update mysql.user set Password=PASSWORD('password') where User = 'user';
flush privileges;

For whatever reason I needed to disable old passwords on the MySQL session that I was connected on.


How to use PHP with MySQL (without SQL Injection vulnerabilities)

1 Comment

Chris Dale recently posted a horrifying article on his blog.  It is called “Why it’s easy being a hacker – A SQL injection case study“.  The most horrifying part of the post was that when you type in the Google search “How to use PHP with MySQL” a significant number of the results come back with some VERY poor examples.  Most of them were vulnerable to SQL injection.  That means that people new to PHP are getting really, really bad advice on how to connect to the database.

With that in mind I want to give a very brief tutorial on how you really should be connecting to MySQL.  It will use only native PHP code and will contain no application architecture examples.  It will also not expound upon larger security issues.  It will simply give you a good starting point for using PHP with MySQL.

My hope in writing this blog post is that enough people will link to this page so-as to increase its page rank so that when people search for “how to use php with mysql” that it (or something like it) will be pre-eminent.  In some ways, my purpose in writing this page is simply Google-bait.

With that in mind, please, please promote this page on your blog, Twitter, Google+, etc.  There are a lot of really bad examples of PHP example code which contains SQL injection vulnerabilities and the only way to negate that is to educate people on the proper way to do it.

What to use

There are three different extensions you can use.  mysql, mysqli and pdo_mysql.  The vanilla mysql extension is deprecated and will be removed.  So if you are using function calls that start with mysql_ then you are using the wrong library.  You should use either the mysqli API or the pdo_mysql API.  PDO places a database abstract layer in between your code and the database driver which makes your code more portable.  It is not perfect in that it does not take care of vendor SQL differences but it does remove the API as a consideration if you need to migrate your database, for the most part.

So for the purpose of this article I will focus only on the PDO driver.  Additionally, there are several different sources for how to connect to MySQL, create tables and execute queries.  For that reason I will focus solely on PHP as an interface and not worry too much about actual SQL.

Setting up PHP

It is recommended that you use the new mysqlnd driver for providing MySQL access.  It adds a lot of new features like lazy connections and query caching.  To compile your PHP with mysqlnd make sure that you configure your source distribution like this

1
./configure --with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd --with-mysql=mysqlnd

Connecting to the database

To connect to the database you will need to create a new PDO object.  This is done with the following code.

1
2
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');

The first argument is called the DSN.  It is a connection string that defines various options for PDO, including which driver to use, in this case mysql.  The second argument is the username to connect to and the third is the password.

You should generally not hard code these values into your application.  They should be retrieved from some configuration source so you don’t need to redeploy your application or edit live code to make a DSN change.

Inserting and updating data

A database is useless without data. In many articles inserting data is done with code similar to this

1
2
3
<?php
 
mysql_query("INSERT INTO `data` VALUES ('$name', '$email', '$location')");

This is wrong.  Horribly wrong.  This code is vulnerable to SQL injection.  Me, as an attacker, can use this code to execute arbitrary SQL statements on your system.

There are two ways to do this right.  One is to use the function mysqli_real_escape_string.  The other is to use prepared statements.  In my opinion prepared statements are much preferred for the following reasons

  1. Statements can be re-used without re-parsing SQL (escaping properly is better, but not best, IMHO)
  2. Prepared statements send data outside of the SQL statement via a binary protocol

 

Using a prepared statement is very easy.  You pass the driver an SQL string but you omit the data, providing placeholders instead.  Because there is no user data being provided an attacker cannot manipulate your SQL statement.

1
2
3
4
5
6
7
8
9
10
<?php
 
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$statement = $pdo->prepare('INSERT INTO customers (name, address) VALUES (?, ?)');
$statement->execute(
  array(
    $_POST['name'],
    $_POST['address']
  )
);

When prepare() is called the statement is not actually executed.  It has no data to work with.  The data is only inserted into the database when the execute() method is called.

If you like, you can also provide named parameters.  In the previous example, the values inserted will correspond to the position in the array.  Using named parameters you can simply specify the placeholder names and not have to worry about the order.

1
2
3
4
5
6
7
8
9
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$statement = $pdo->prepare('INSERT INTO customers (name, address) VALUES (:name, :address)');
$statement->execute(
  array(
    'address' => $_POST['address'],
    'name' => $_POST['name']
  )
);

Updating is done in virtually the exact same way, except with an UPDATE query.

1
2
3
4
5
6
7
8
9
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$statement = $pdo->prepare('UPDATE customers SET name = :name WHERE name = :oldname');
$statement->execute(
  array(
    'oldname' => $_POST['oldname'],
    'name' => $_POST['name']
  )
);

Retrieving data

Retrieving data is done via a SELECT call.  To start, do the same thing that you did before by creating a prepared statement.  However, this time we are going to iterate over the result set using the fetch() method.  You have four different options when retrieving data.  You can use fetch() to fetch a row, fetchAll() to fetch all rows in a multi-dimentional array, fetchColumn() to only get data for an individual column or fetchObject() to retrieve the row as an object of a specific type, or of stdClass if no type is provided.

I will only show fetch().

1
2
3
4
5
6
7
8
9
10
11
12
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$statement = $pdo->prepare('SELECT address FROM customers WHERE name = ?');
$statement->execute(
  array(
    'Kevin Schroeder'
  ) 
);
 
while(($data = $statement->fetch()) !== false) {
  echo htmlspecialchars($data['address']) . '<br />';
}

However, there is a bit of a problem.  MySQL does not allow you to pass certain things in as a prepared statement parameter, such as a LIMIT clause.   The variables that you can provide are generally limited to data.  Because of that you may need to take some care in constructing prepared SQL statements that do require “inline” user-submitted data, like a LIMIT clause.  Thankfully, that can easily be taken care of with casting.

1
2
3
<?php
$limit = (int)$_POST['limit'];
$statement = $pdo->prepare('SELECT address FROM customers WHERE name = ? LIMIT ' . $limit);

You will note that I put the LIMIT value in a separate variable.  I do this because it makes for easier debugging.  It is tough to use a debugger if you put data transformations in a method calls since it will not show up in your variable list.

Deleting data

Deleting data is pretty much the same as selecting data, but you don’t have a record set to iterate over.

1
2
3
4
5
6
7
8
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$statement = $pdo->prepare('DELETE FROM customers WHERE name = :name');
$statement->execute(
  array(
    'name' => $_POST['name']
  )
);

Related Links

Conclusion

This blog post is not meant to give a full introduction to SQL operations on PHP, nor is it intended to showcase secure PHP programming practices.  Rather, it is intended to showcase basic PHP/SQL usage in a manner that is consistent with security best practices… unlike the other billion “PHP and MySQL” pages out there.  If there is something I’ve missed, or said incorrectly, please feel free to leave a comment.

Also, please share this page as much as possible so that its page rank is increased.  Also, if you have good links to highlight how to properly work with SQL please leave them in the comments and I will put them in the related links section.  If people really are getting lots of bad PHP/MySQL info let’s see if we can raise the tide of the good examples over the bad ones.

 


Using Mongo with PHPCloud

3 Comments

With the release that occurred on March 4th we have added Mongo support to the DevCloud.  So connecting to a Mongo instance is extremely easy to do now.  We don’t provide access to a Mongo collection but you do have the tools to connect to various Mongo service providers.  For this example I used MongoHQ though any service running Mongo sound work.

First go to whichever service you are using and create a new collection.  Like this

Then click on the Zend Server link for your container.  Like this

Go to Server Setup / Extensions and enable Mongo.  Like this

Restart Zend Server and deploy code.  Like this

1
2
3
4
5
6
7
8
9
10
11
$db = new Mongo('mongodb://kschroeder:[email protected]:10050/kschroeder');
 
$collection = $db->kschroeder->views;
 
$collection->insert(
array(
'view' => new MongoDate()
)
);
 
echo $collection->count() . ' views on this page';

Now you’re doing Mongo on the DevCloud like a boss.

It took me all of 10 minutes to do this.