How do you escape ' on doctrine?

user_1942868 0
Viewed 16949

How do you escape ' on doctrine?

I made this code

$query = $em->createQuery(
                "SELECT a FROM AcmeTopBundle:ArtData a WHERE
                a.name = '". mysql_escape_string($name) ."'");

but when the $name is A'z

it returns error 

[Doctrine\ORM\Query\QueryException]          
SELECT a FROM AcmeTopBundle:ArtData a WHERE  
                a.name = 'A\'s'

I think I escaped by mysql_escape_string in case of using raw sql.

How can I avoid this error on doctrine?

doctrine-orm php
5 Answers

Well, even though there is accepted answer it is not for question as it is in title. @Sven's answer comes close, but fails to mention:

Doctrine documentation

To escape user input in those scenarios use the Connection#quote() method.

And I have a gripe with "scenarios", or more with people pushing prepared statements like some holy grail. Well they are nice in theory, in practice at least in PHP they are quite shity, as they are unable to do simple stuff like IN (<list>) or multi inserts with VALUES (<bla bla>), (<more stuff>) which is a huge ass deal, as without it one ends up resorting to quite sub-optimal SQL (to put it lightly) quite commonly (well if one religiously insist on prepared statements at least).

user_5862210

This will show how to insert the data into the database where you would normally have to use real_escape_string.

Doctrine and Symfony 3 using prepared not QueryBuilder:

// get the post value
$value = $request->request->get('value'); 

$sql = "INSERT INTO `table_name`
(`column_name1`,`column_name2`)
VALUES
('Static Data',?)
";

$em = $this->getDoctrine()->getManager();
$result = $em->getConnection()->prepare($sql);
$result->bindValue(1, $value);
$result->execute();

Now for a bonus to get a success/fail if you are using auto increment records:

$id = $em->getConnection()->lastInsertId();

if $id has a value then it executed the insert. If it does not the insert failed.

user_7927650
Related