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()?

Escape the backslash. `UPDATE \`setting\` SET \`classname\` = 'org\\foo\\Bar' WHERE \`id\` = 1 ` — Charlotte Dunois, Apr 18 '16 at 15:08
@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. — Ray, Apr 18 '16 at 15:16
@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. — Ray, Apr 18 '16 at 15:20
The issue [#486](https://github.com/robmorgan/phinx/pull/486#issuecomment-192810929) on github on this suggests that you get the PDO connection from Phinx and normally use Prepared Statements as documented on http://php.net/PDO — Charlotte Dunois, Apr 18 '16 at 15:23

Ray · Accepted Answer · 2016-04-20T13:26:04.010

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

Grab the the PDO connection
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 ");
 }

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

1 Answers1