Search code examples
mysqldoctrinequery-builderdbal

Is there any benefit in using parameters for query parameters that dont change?

I am writing methods in PHP classes that use QueryBuilder for the DBAL (Doctrine) ORM.

One of the thing I like about this is that we can easily use parameters in the SQL that is generated. However, as I was working on a few queries and I notice that there are many instances where the query has parameters that dont change (like "WHERE is_active = 1") where the "1" has been parameterized.

Is there any benefit to parameterizing these values that are written in these functions that aren't ever truly in line or exposed?

example:

$this->db->select('u.LastName, u.FirstName, s.*')
         ->from('scores', 's' )
         ->join( 's', 'user', 'u', 's.user_id = u.id')
         ->where( "s.active = :is_active")
         ->andWhere("s.id = :user_id" )
         ->setParameters(['is_active' => 1, 'user_id' => $user_id]);

versus something like this:

$this->db->select('u.LastName, u.FirstName, s.*')
         ->from('scores', 's' )
         ->join( 's', 'user', 'u', 's.user_id = u.id')
         ->where( "s.active = 1")
         ->andWhere("s.id = :user_id" )
         ->setParameter('user_id', $user_id);

I am not usually a Belt and Suspenders guy, (meaning I do it just to be safe), if there is no benefit, then I probably wouldn't do it.

I have looked at the DBAL docs and MySQL docs and I just don't see anything that tells me one way or the other. Maybe someone else has better Google-Fu or personal experience.

Thanks in advance,

GaryC.

Solution

  • There is no technical benefit. If the value is always a constant 1, you might as well make it a literal in the query, as you show in your second example.

    There may be a benefit that is a developer culture issue, not a technical benefit. If some members of your developer team are novice and have a hard time understanding when to use a parameter and when it's safe not to use a parameter, then giving them a guideline to always use a parameter for any value in any SQL expression at least means they will use parameters when they really need to do so.