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.
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).
'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();