1

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

INSERT INTO failed_logins 
SET email = 'example@test.com', 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.

Samuel Gfeller
  • 840
  • 9
  • 19
  • The custom functions section of the referenced manual seems to be the answer to your question. Have a go at it! – Shadow Mar 04 '21 at 08:13
  • Probably but I'm too dumb to make something out of it. The `func()` method is used on the query object and I make the insert with `$this->connection->insert('table', $row)` there is no query involved but I'm missing out on something I know. – Samuel Gfeller Mar 04 '21 at 08:26
  • Is there a specific reason for you to use the connection instead of the ORM, or even the query builder? – ndm Mar 04 '21 at 12:33
  • @ndm I posted an edit. – Samuel Gfeller Mar 04 '21 at 13:19
  • I dind't say that you don't need to use `insert()`, if you want to create an insert query, you definitely need to call `insert()`, but you don't necessarily have to use the connection's convenience function, which is just a wrapper around the query builder insert with a retry strategy for the connection. – ndm Mar 04 '21 at 13:32
  • Yes I saw my mistake and corrected it shortly after. The query I built made no sense and I've FINALLY come to a solution now. – Samuel Gfeller Mar 04 '21 at 13:39

1 Answers1

1

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();
Samuel Gfeller
  • 840
  • 9
  • 19
  • I can assure you that you wouldn't have loved that, because that would require the right hand side value to not be bound/escaped, which would leave your code wide open for SQL injections! ;) As an alternative to expressions you can however always use [**manual bindings**](https://book.cakephp.org/4/en/orm/query-builder.html#binding-values) `'INET_ATON(:ip)'` and then `->bind(':ip', $ip, 'string')` on the query builder. ps, be careful with that `literal`, that is ment to insert data _as is_ (but it would need to be in the value side to work), which again would be SQL injection prone! – ndm Mar 04 '21 at 13:59
  • You are totally right. In this case the `$ip` comes from `$_SERVER['REMOTE_ADDR']` which safe as the client cannot modify it at will. I saw that I could (dirtily) archive what I wanted with `$query->newExpr("INET_ATON($ip)");` its clear however that this must not be used with untrusted values but would you allow it here or not? The manual binding is very interesting, I didn't know about that! I think I will do it like this; makes the most sense for me. – Samuel Gfeller Mar 04 '21 at 14:11
  • 1
    I would definitely never ever do that unless it would be 1001% actually technically required! But even then I would use whitelists or manually escape the input if applicable. While your code may for now use a value that stems from a safe source, there is no guarantee that it will stay that way, one day you might be a little careless when making changes to your code, or someone else who isn't aware about where/how the value is being used makes changes, and suddenly the input stems from an untrusted source. You never know what the future holds. – ndm Mar 04 '21 at 14:23
  • Right this is very possible. Always stay safe and bind stuff I'll remember. I like the edit now with `->bind` thanks a lot for this suggestion. – Samuel Gfeller Mar 04 '21 at 14:36