Escape variable for regular mysql query using pw connection.

[joer80]

Ok, I am wanting to know the best practice for escaping a variable to prepare it for an sql insert when I am not using selectors.  Its a traditional mysql query, but I want to use the same connection variables processwire uses.

 

Without escaping it, it will throw an error out because $comment has an unescaped ' in it.

 

What is the proper way to escape out the comment using the processwire database connection information?

I tried the $comment= $this->wire('database')->quote($comment); but that doesnt seem to work.

 

Thanks!

 

 

Code:

 

$comment = "The boat is 17' long. ";

$sql= "INSERT INTO tablename (Comments) VALUES ('$comment')";
$database = $this->wire('database'); 
$database->exec($sql);

 

[joer80]

Think I have it!

It doesn't appear necessary to pass a connection.  

This worked:

 

$comment = "The boat is 17' long. ";
$comment = mysql_real_escape_string($comment);
$sql= "INSERT INTO tablename (Comments) VALUES ('$comment')";
$database = $this->wire('database'); 
$database->exec($sql);

[gRegor]

I'd recommend using prepared statements. ProcessWire's database class extends mysqli, so you can do:

$statement = $this->database->prepare('INSERT INTO tablename (Comments) VALUES (?)');
$result = $statement->execute(array($comment));

(This is written off the top of my head, not tested)

[netcarver]

@joer80,

You should be able to use the PDO prepare(), bind() and execute() methods. Here's a short example;

$statement = wire()->database->prepare("INSERT INTO `tablename` (`comments`) VALUES (:comment)");
$statement->bindParam(":comment", "The boat is 17' long. "); // Defaults to binding as a string.
$statement->execute();

[joer80]

These work great!  I would much rather do it this way and use pdo.

This is the working code I am using:

 

 $statement = wire()->database->prepare('INSERT INTO tablename (comment) VALUES (:comment)');
 $statement->bindParam(":comment", $comment);
 $statement->execute();