How to Add a Column Name in a Case Statement for SQL in CakePHP

kbn, name is the column name, but I want the |name part to be recognized as a string and recognized as a column name in the table. How should I describe it?

SQL Statement

select 
 case when kbn = '1' then '1' || name 
 else '2' 
 end username 
from user;

CakePHP

$username=$query->newExpr()->addCase(
                [$subquery ->newExpr()->add('kbn'=>'1')],
                ["1'||name", new IdentifierExpression('2')],
                ["string", "string"]
            );

cakephp

2022-12-07 10:51

1 Answers

If '1' and '2' are not external input values, I think you can write SQL as it is in newExpr.
(Assume SQL injection does not occur)

$expr=$query->newExpr("case when kbn='1'then'1'||name else'2'end");
$query->select(['username'=>$expr]);

If there is a possibility of external input, try to bind the value considering SQL injection.

$expr=$query->newExpr()->addCase(
    $query->newExpr()->eq('kbn', '1'),
    [
        $query->newExpr()->or ([':p1', $query->identifier('name')],
        $query->newExpr(':p2'),
    ],
    ["string", "string"]
);
$query->bind(':p1', '1');
$query->bind(':p2', '2');
$query->select(['username'=>$expr]);

Use IdentifierExpression to specify an identifier, such as a field name in a table.
$query->identifier('field_name') is the synonym for new IdentifierExpression('field_name').

Use Identifier in Expression|Query Builder - 4.x

2022-12-07 13:43

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656