How do you avoid the "doctrine"?

Question

How do you avoid learning?

I made this code

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

but when $ name - A'z

he returns an error

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

I think I avoided mysql_escape_string when using raw sql.

How can I avoid this doctrine error?

Answer 1

I usually handle this with parameters and querybuilder ( http://docs.doctrine-project.org/en/latest/reference/query-builder.html ) ...

 $qb = $em->createQueryBuilder( "SELECT a FROM AcmeTopBundle:ArtData a WHERE a.name = :name") ->setParameter('name',$name); $result = $qb->getQuery()->execute(); 

Answer 2

Based on https://stackoverflow.com/a/318618/

you can use prepared statements http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#using-prepared-statements

From the documentation:

 $date = new \DateTime("2011-03-05 14:00:21"); $stmt = $conn->prepare("SELECT * FROM articles WHERE publish_date > ?"); $stmt->bindValue(1, $date, "datetime"); $stmt->execute(); 

Answer 3

This does not answer your question, but explains what is wrong with your code. He did not fit into the comment.

You cannot and should not use mysql_escape_string()

• This is the wrong evacuation function, the correct one is mysql_real_escape_string() . Reading the documentation is not like it, but you need to know which character encoding is used to properly log out. In Western encoding schemes such as ASCII, ISO-8859-x, or even UTF-8, this probably doesn't matter, but there are some exotic Chinese encodings around which it is absolutely necessary to know whether this byte belongs to another byte or comes on his own.
• When using mysql_real_escape_string (), you need to have an already open database connection created using mysql_connect (). If you do not, PHP will try to open a new connection with the default user and password, as defined in the php.ini file. This usually results in an error, because without a password, the database will not allow you to do anything. In addition, if you have success, then setting up the encoding of this connection is most likely not the one used in Doctrine.
• Using any of the mysql_ * functions is incorrect because they are deprecated. The correct way is to use mysqli_ * functions.
• Doctrine can use any of the three database connection methods: mysql, mysqli, or PDO. You must select the one that is actually used if you want to manually call the correct escaping function. While the connection has already been created. And somehow you need to capture this connection resource to allow the function you are calling to detect the encoding used.

So, after all, there are many reasons why it's wrong to use any shielding that looks like it is doing this job.

The correct way is to use the escaping of the database level you are using. If you use Doctrine, use it for escaping. Or better, avoid escaping, use prepared statements or a query builder and let the Doctrine deal with the rest.

Answer 4

This will show how to insert data into a database where you usually need to use real_escape_string.

Doctrine and Symfony 3 using non-QueryBuilder prepared:

 // 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, to receive a bonus, get success / failure if you use automatic growth records:

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

if $ id matters, then it performed an insert. If this did not happen, the insert failed.