How do you escape strings when writing data Migrations in Phinx?

Question

I've got some simple updates inside my migration with strings that may contain special characters. For example:

$this->execute("UPDATE `setting` SET `classname` = 'org\foo\Bar' WHERE `id` = 1 ");

The problem with this for example, org\foo\Bar when inserted into MySQL treats \ as escape characters. For each DB phinx supports, I'm sure there are special characters that need to be handled in strings that when using PDO directly you'd get around by using prepared statements and binding parameters.

Is there any native way in phinx to escape strings or do I need to fall back on something like PDO::quote()?

Katelyn

Escape the backslash. UPDATE `setting` SET `classname` = 'org\\foo\\Bar' WHERE `id` = 1 – Jacklight

@CharlotteDunois yes, I can manually escape them for MySQL, but escaping this is a DB-by-DB task. The backslashes are just one example, quotes, double dashes and such also need to be considered. To make it generic across db's (work for sqlite, postgres, etc...), you don't want to manually escape the string. – Katelyn

Use Prepared, Parameterized Queries. – Jacklight

@CharlotteDunois Do you have a link with an example on how to use Prepared, Parameterized Queries in phinx, or add an example as an answer to this question. I can't seem to find documentation on how to accomplish this via good ole google. – Katelyn

The issue #486 on github on this suggests that you get the PDO connection from Phinx and normally use Prepared Statements as documented on php.net/PDO – Jacklight

Answer 1

As alluded to in Charlotte's OP comments, it doesn't look like this feature exists. The work around is the following:

1. Grab the the PDO connection
2. Use the quote() or manually construct a query using the connection directly

Here's my code example using quote()

public function change()
{
    $conn = $this->getAdapter()->getConnection();
    $quotedString = $conn->quote('org\foo\Bar');
    $this->execute("UPDATE `setting` SET `classname` = $quotedString WHERE `id` = 1 ");
 }

Katelyn