Attention: If you are using positional parameters with the query builder you have
to be very careful to bind all parameters in the order they appear in the SQL
statement , otherwise they get bound in the wrong order which can lead to serious
bugs in your code.
Example:
$qb = $conn->createQueryBuilder();
$qb->select('u.*')
->from('users', 'u')
->where('u.username = ' . $qb->createPositionalParameter('Foo', PDO::PARAM_STR))
->orWhere('u.username = ' . $qb->createPositionalParameter('Bar', PDO::PARAM_STR))
/**
* @param QueryBuilder $builder
* @param $column
* @param $value
* @throws Exception
* @return string
*/
public function __invoke(QueryBuilder $builder, $column, $value)
{
if (is_array($value) && !empty($value)) {
return $column . ' NOT IN (' . $builder->createPositionalParameter($value, Connection::PARAM_STR_ARRAY) . ')';
}
if ($value === null || is_array($value) && empty($value)) {
return $column . ' IS NOT NULL';
}
return $column . ' != ' . $builder->createPositionalParameter($value);
}
/**
* @param QueryBuilder $builder
* @param $column
* @param $value
* @throws Exception
* @return string
*/
public function __invoke(QueryBuilder $builder, $column, $value)
{
if (!is_array($value)) {
throw new Exception("Use of IN operator expects value to be array. Got " . gettype($value) . ".");
}
return $column . ' IN (' . $builder->createPositionalParameter($value, Connection::PARAM_STR_ARRAY) . ')';
}
/**
* @test
*/
public function createPositionalParameterDelegatesToConcreteQueryBuilder()
{
$this->concreteQueryBuilder->createPositionalParameter(5, Argument::cetera())->shouldBeCalled()->willReturn('?');
$this->subject->createPositionalParameter(5);
}
public function testCreatePositionalParameter()
{
$qb = new QueryBuilder($this->conn);
$qb->select('u.*')->from('users', 'u')->where($qb->expr()->eq('u.name', $qb->createPositionalParameter(10, \PDO::PARAM_INT)));
$this->assertEquals('SELECT u.* FROM users u WHERE u.name = ?', (string) $qb);
$this->assertEquals(10, $qb->getParameter(1));
}
/**
* @param QueryBuilder $builder
* @param $column
* @param $value
* @return string
*/
public function __invoke(QueryBuilder $builder, $column, $value)
{
return $column . ' >= ' . $builder->createPositionalParameter($value);
}
/**
* Creates a new positional parameter and bind the given value to it.
*
* Attention: If you are using positional parameters with the query builder you have
* to be very careful to bind all parameters in the order they appear in the SQL
* statement , otherwise they get bound in the wrong order which can lead to serious
* bugs in your code.
*
* Example:
* <code>
* $qb = $conn->getQueryBuilder();
* $qb->select('u.*')
* ->from('users', 'u')
* ->where('u.username = '******'Foo', IQueryBuilder::PARAM_STR))
* ->orWhere('u.username = '******'Bar', IQueryBuilder::PARAM_STR))
* </code>
*
* @param mixed $value
* @param integer $type
*
* @return IParameter
*/
public function createPositionalParameter($value, $type = IQueryBuilder::PARAM_STR)
{
return new Parameter($this->queryBuilder->createPositionalParameter($value, $type));
}
/**
* Creates a new positional parameter and bind the given value to it.
*
* Attention: If you are using positional parameters with the query builder you have
* to be very careful to bind all parameters in the order they appear in the SQL
* statement , otherwise they get bound in the wrong order which can lead to serious
* bugs in your code.
*
* @param mixed $value
* @param int $type
*
* @return string
*/
public function createPositionalParameter($value, int $type = \PDO::PARAM_STR) : string
{
return $this->concreteQueryBuilder->createPositionalParameter($value, $type);
}
/**
* @param QueryBuilder $builder
* @param $column
* @param $value
* @return string
*/
public function __invoke(QueryBuilder $builder, $column, $value)
{
return 'MATCH(' . $column . ') AGAINST (' . $builder->createPositionalParameter($value) . ')';
}
/**
* Creates a new positional parameter and bind the given value to it.
*
* @param mixed $value
* @param integer $type
*
* @return string
*/
public function createPositionalParameter($value, $type = \PDO::PARAM_STR)
{
return $this->qb->createPositionalParameter($value, $type);
}