Custom functions for non-select queries Cake Query Builder

I would like to use the mariadb INET_ATON() on an insert query with Cakephp Query Builder.

INSERT INTO failed_logins 
SET email = '[email protected]', ip_address = INET_ATON('192.168.0.1'), sent_email = 1;

Then I’d like to retrieve the data with INET_NTOA() in a select query.

SELECT id, email, INET_NTOA(ip_address) AS ip_address, sent_email FROM failed_logins;

How do I use these functions with an insert and select on the Cake Query Builder?
I saw Using SQL functions but couldn’t solve my issue.

Answer

After a lot of playing around I managed to make it work.

$this->connection->newQuery()->into('failed_logins');
$newIp = $query->func()->inet_aton([$ip]);
$query->insert(['email', 'ip_address', 'sent_email'])->values(
    ['email' => $email, 'ip_address' => $newIp, 'sent_email' => $sentEmail]
)->execute()->lastInsertId();

Quite complicated and my IDE and PHPStan show me warnings that the function “inet_aton” is not defined.
I would have loved it if in the values() array I could have just done it like ['ip_address' => "INET_ATON($ip)"]. Edit: This is not a good idea see comments. But something similar that stays safe can be done with ->bind() (code snippet below).

Edit: Removed 'literal' from the code snippet (thanks @ndm)

IDE and Analysis Tool – friendly solution

$this->connection->newQuery()->into('failed_logins');

$query->insert(
    [
        'email',
        'ip_address',
        'sent_email',
    ]
)->values(
    [
        'email' => $email,
        'ip_address' => $query->newExpr("INET_ATON(:ip)"),
        'sent_email' => $sentEmail,
    ]
)->bind(':ip', $ip, 'string')->execute()->lastInsertId();

Leave a Reply

Your email address will not be published. Required fields are marked *