Bypassing MODX to Write Directly to the Database

In some previous articles, we discussed fast ways to get data from the database in MODX Revolution, but what if you want to update the database? Suppose that you also want to bypass MODX to write a raw value directly to the database.

In this article, we’ll look at a fast way to set specific object fields using straight MySQL. In the following article, we’ll look at another way that’s more convenient but not quite as fast.

 Why?

One reason for bypassing MODX is to speed things up, but it’s not a very good reason. The time advantage of using this method is pretty small compared to using xPDO’s set() and MODX logosave().

A better reason is to bypass the processing that MODX does when you use set(). In a number of cases MODX converts the data sent in the set() method before it’s stored in the database (and converts it the other way when you call get() or toArray()). The dates that you enter for the publish and unpublish dates when editing a resource are converted into Unix timestamps for storage in the database. Likewise, the extended field in the user profile, and the properties field for elements and resources is converted from a PHP array into a JSON string when you store it.

For example, suppose you want to write some modified snippet properties to the database. Typically, you would create an array and pass that array to set(). The set() method, seeing that it’s the properties field, will convert that array to a JSON string and store it in the properties field. But what if you already have the properties in the form of a JSON string? It’s silly and wasteful (though not very time consuming) to convert them to an array just so that MODX can convert them back to the way they are now.

Here’s a much better example that happened to me recently and prompted me to write this Blog post. In this case, it was *critical* to write raw values to the database.

I was moving some users from MODX Evolution to MODX Revolution for a client. The users’ passwords were in the form of a MD5 hash with no salt. If I used $user->set('password', $password), MODX would hash the already hashed password again and it would never work. I needed to write the password to the password field in the database without going through the user object’s set() method.

The Method

Here’s a simple example that updates the modUser fields of the user with the ID 12:

[code language=”php”]
$password = ‘SomePassword’;
$userId = 12;

$table = $modx->getTableName(‘modUser’);
$stmt = $modx->prepare("UPDATE " . $table . " SET `password`=’" .
$password . "’, `hash_class`=’hashing.modMD5′, `cachepwd`=”,
`salt`=” WHERE id=" . $userId);

/* For debugging */
echo $stmt->queryString;

$stmt->execute();
[/code]

The code above will display something like this (all on one line):

[code language=”html”]
UPDATE `modx_users` SET `password`=’SomePassword’, `hash_class`=’hashing.modMD5′,
`cachepwd`=”, `salt`=” WHERE id=12
[/code]

If we used set() for these variables, MODX would hash the password (which is already hashed), and might automatically create values for the salt and cachepwd fields. The user would not be able to log in. Using the code above, we’re able to write the raw values directly to the database.

How it Works

Since our code is ultimately using PDO to execute the query, we can’t just refer to the modUser object directly because PDO doesn’t know about that object. Instead, we need to get the name of the actual table the query will be operating on with $modx->getTableName().

Next, we need to prepare the query with $modx->prepare(), which just calls PDO->prepare(). Note that the prepare() function is actually in the xPDO class, but since $modx extends xPDO, it will work as long as you’re inside MODX when the statement executes.

Finally, we execute the prepared statement with $stmt->execute. Because $stmt is an instance of the PDOStatement object, this calls that object’s execute() member (PDOStatement::execute()).

Sanity Checks

The code above will be fine for development, but before it’s installed on a production site, it’s a good idea to add some sanity checks to make sure a malformed query doesn’t throw a tactless error. We’ll make sure that prepare() was successful (it returns false on failure), and that $stmt is a valid instance of PDOStatement. We also want to take out the echo statement we used for development. The calls to $modx->log() will write error messages to the MODX Error Log.

[code language=”php”]
$password = ‘SomePassword’;
$userId = 12;

$table = $modx->getTableName(‘modUser’);
$stmt = $modx->prepare("UPDATE " . $table . " SET `password`=’" .
$password . "’, `hash_class`=’hashing.modMD5′, `cachepwd`=”,
`salt`=” WHERE id=" . $userId);

/* echo $stmt->queryString; */

if (is_object($stmt) && $stmt instanceof PDOStatement) {
if (! $stmt->execute()) {
$modx->log(modX::LOG_LEVEL_ERROR, "Error " . $stmt->errorCode() .
" executing statement: \n" . print_r($stmt->errorInfo(), true),
”, __METHOD__, __FILE__, __LINE__);
}
} else {
$modx->log(modX::LOG_LEVEL_ERROR, "Invalid PDOStatement", ”, __METHOD__, __FILE__, __LINE__);
}
[/code]

The error logging above is a little complex (we’ll discuss it further in another article). It provides a fair amount of information about the error and where it occurred. In many cases, you can get by with just two arguments, the first will be the “log level”, which is almost always modX::LOG_LEVEL_ERROR. The second is just a string containing the text of the error message (or a call to $modx->lexicon()). In our example above, you could use these two calls to the log:

[code language=”php”]
if (is_object($stmt) && $stmt instanceof PDOStatement) {
if (! $stmt->execute()) {
$modx->log(modX::LOG_LEVEL_ERROR, "Error executing statement: " . $stmt->queryString);
}
} else {
$modx->log(modX::LOG_LEVEL_ERROR, "Invalid PDOStatement");
}
[/code]

For an even briefer version, you can also do this:

[code language=”php”]
if ($stmt && $stmt->execute()) {
/* Success */
} else {
/* Failure */
}
[/code]

In the next article, we’ll look at a more convenient way to write values directly to the database with xPDO using its fromArray() method.


For more information on how to use MODX to create a web site, see my web site Bob’s Guides, or better yet, buy my book: MODX: The Official Guide.

Looking for quality MODX Web Hosting? Look no further than Arvixe Web Hosting!

Tags: , , , | Posted under MODX, MODX | RSS 2.0

Author Spotlight

Bob Ray

Bob Ray is the author of MODX: The Official Guide and over 30 MODX add-on components. He hosts Bob's Guides, a source of valuable information for MODX users, and has been very active in the MODX Forums with over 19,000 posts.

Leave a Reply

Your email address will not be published. Required fields are marked *