{"action":"created","issue":{"url":"https://api.github.com/repos/cakephp/cakephp/issues/7157","labels_url":"https://api.github.com/repos/cakephp/cakephp/issues/7157/labels{/name}","comments_url":"https://api.github.com/repos/cakephp/cakephp/issues/7157/comments","events_url":"https://api.github.com/repos/cakephp/cakephp/issues/7157/events","html_url":"https://github.com/cakephp/cakephp/issues/7157","id":98486328,"number":7157,"title":"3.x SQL Injection Issue and/or Questions","user":{"login":"ionas","id":106845,"avatar_url":"https://avatars.githubusercontent.com/u/106845?v=3","gravatar_id":"","url":"https://api.github.com/users/ionas","html_url":"https://github.com/ionas","followers_url":"https://api.github.com/users/ionas/followers","following_url":"https://api.github.com/users/ionas/following{/other_user}","gists_url":"https://api.github.com/users/ionas/gists{/gist_id}","starred_url":"https://api.github.com/users/ionas/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ionas/subscriptions","organizations_url":"https://api.github.com/users/ionas/orgs","repos_url":"https://api.github.com/users/ionas/repos","events_url":"https://api.github.com/users/ionas/events{/privacy}","received_events_url":"https://api.github.com/users/ionas/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"markstory","id":24086,"avatar_url":"https://avatars.githubusercontent.com/u/24086?v=3","gravatar_id":"","url":"https://api.github.com/users/markstory","html_url":"https://github.com/markstory","followers_url":"https://api.github.com/users/markstory/followers","following_url":"https://api.github.com/users/markstory/following{/other_user}","gists_url":"https://api.github.com/users/markstory/gists{/gist_id}","starred_url":"https://api.github.com/users/markstory/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/markstory/subscriptions","organizations_url":"https://api.github.com/users/markstory/orgs","repos_url":"https://api.github.com/users/markstory/repos","events_url":"https://api.github.com/users/markstory/events{/privacy}","received_events_url":"https://api.github.com/users/markstory/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/cakephp/cakephp/milestones/53","html_url":"https://github.com/cakephp/cakephp/milestones/3.0.11","labels_url":"https://api.github.com/repos/cakephp/cakephp/milestones/53/labels","id":1219730,"number":53,"title":"3.0.11","description":"","creator":{"login":"lorenzo","id":37621,"avatar_url":"https://avatars.githubusercontent.com/u/37621?v=3","gravatar_id":"","url":"https://api.github.com/users/lorenzo","html_url":"https://github.com/lorenzo","followers_url":"https://api.github.com/users/lorenzo/followers","following_url":"https://api.github.com/users/lorenzo/following{/other_user}","gists_url":"https://api.github.com/users/lorenzo/gists{/gist_id}","starred_url":"https://api.github.com/users/lorenzo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lorenzo/subscriptions","organizations_url":"https://api.github.com/users/lorenzo/orgs","repos_url":"https://api.github.com/users/lorenzo/repos","events_url":"https://api.github.com/users/lorenzo/events{/privacy}","received_events_url":"https://api.github.com/users/lorenzo/received_events","type":"User","site_admin":false},"open_issues":23,"closed_issues":39,"state":"open","created_at":"2015-07-22T07:42:38Z","updated_at":"2015-08-01T15:47:06Z","due_on":"2015-08-01T22:00:00Z","closed_at":null},"comments":18,"created_at":"2015-07-31T23:10:21Z","updated_at":"2015-08-01T15:59:22Z","closed_at":null,"body":"```php\r\n/**\r\n *      SET @iso_3166_1_alpha_2_country_code = 'DE';\r\n *      SET @zip_code = '80687';\r\n *      # Will find similar to \"Agnes Pernaur Platz\" and correct \"Agnes-Bernauer-Platz\" in geodata\r\n *      SET @str = 'Agnes Pernaur Plat';\r\n *      SET @levenshtein_tolerance = 100;\r\n *   # EXPLAIN EXTENDED\r\n *   SELECT street\r\n *     FROM (    \r\n *        SELECT street, 1 AS table_order\r\n *          FROM geodata AS soundex_table\r\n *         WHERE iso_3166_1_alpha_2_country_code = @iso_3166_1_alpha_2_country_code\r\n *           AND zip_code = @zip_code\r\n *           AND SOUNDEX(LOWER(@str)) = SOUNDEX(LOWER(street))\r\n *         UNION ALL\r\n *        SELECT street, 2 AS table_order\r\n *          FROM geodata AS levenshtein_table\r\n *         WHERE iso_3166_1_alpha_2_country_code = @iso_3166_1_alpha_2_country_code\r\n *           AND zip_code = @zip_code\r\n *           AND levenshtein(@str, LOWER(street)) BETWEEN 0 AND @levenshtein_tolerance\r\n *      ORDER BY levenshtein(@str, LOWER(street)) ASC\r\n *     ) AS geodata\r\n * ORDER BY table_order ASC\r\n *    LIMIT 1;\r\n */\r\npublic function findSimilarAddress(\r\n    $iso31661Alpha2CountryCode, $zipCode, $city, $street, $levenshteinTolerance = null)\r\n{\r\n    $levenshteinTolerance = (int)$levenshteinTolerance == 0 ? 5 : (int)$levenshteinTolerance;\r\n    $street = mb_strtolower($street);\r\n    $queryA = $this->find()\r\n        ->select(['street', 'table_order' => 1])\r\n        ->where([\r\n            'iso_3166_1_alpha_2_country_code' => $iso31661Alpha2CountryCode,\r\n            'zip_code' => $zipCode,\r\n            'city' => $city,\r\n            'SOUNDEX(LOWER(street))' => 'SOUNDEX(' . $street . ')',\r\n        ]);\r\n    $queryB = $this->find()\r\n        ->select(['street', 'table_order' => 2])\r\n        ->where([\r\n            'iso_3166_1_alpha_2_country_code' => $iso31661Alpha2CountryCode,\r\n            'zip_code' => $zipCode,\r\n            'city' => $city,\r\n        ])\r\n        ->andWhere(function ($exp, $q) use ($levenshteinTolerance, $street)  {\r\n            return $exp->between(\r\n                'levenshtein(\"' . addslashes($street) . '\", LOWER(street))', 0, $levenshteinTolerance);\r\n        })\r\n        ->order('levenshtein(\"' . addslashes($street) . '\", LOWER(street)) ASC')\r\n        ->unionAll($queryA);\r\n    $query = $this->find()\r\n        ->select(['Geodata__street' => 'Geodata__street'])\r\n        ->from(['Geodata' => $queryB])\r\n        ->order('table_order ASC')\r\n        ->limit(1);\r\n    return $query;\r\n}\r\n```\r\n\r\n1. Could there be done anything against the requirement of manually adding injection projection (here with a quick ```addslashes()``` hack cause I don't know how to access ```PDO::quote()```)?\r\nEdit: Probably $this->connection()->quote($var). See below.\r\n\r\n2. Book does not document ```->from()```: http://book.cakephp.org/3.0/en/orm/query-builder.html\r\n\r\n3. Had to do a rather dirty hack to get this working: ```->select(['Geodata__street' => 'Geodata__street'])```. Is there a better approach?\r\n\r\nNote: ```levenshtein()``` is a custom MySQL stored procedure. Ref: https://github.com/vyper/levenshtein-sql\r\nPostgreSQL has native support. Ref: http://www.postgresql.org/docs/9.1/static/fuzzystrmatch.html#AEN138328"},"comment":{"url":"https://api.github.com/repos/cakephp/cakephp/issues/comments/126932283","html_url":"https://github.com/cakephp/cakephp/issues/7157#issuecomment-126932283","issue_url":"https://api.github.com/repos/cakephp/cakephp/issues/7157","id":126932283,"user":{"login":"ndm2","id":5031606,"avatar_url":"https://avatars.githubusercontent.com/u/5031606?v=3","gravatar_id":"","url":"https://api.github.com/users/ndm2","html_url":"https://github.com/ndm2","followers_url":"https://api.github.com/users/ndm2/followers","following_url":"https://api.github.com/users/ndm2/following{/other_user}","gists_url":"https://api.github.com/users/ndm2/gists{/gist_id}","starred_url":"https://api.github.com/users/ndm2/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ndm2/subscriptions","organizations_url":"https://api.github.com/users/ndm2/orgs","repos_url":"https://api.github.com/users/ndm2/repos","events_url":"https://api.github.com/users/ndm2/events{/privacy}","received_events_url":"https://api.github.com/users/ndm2/received_events","type":"User","site_admin":false},"created_at":"2015-08-01T15:59:22Z","updated_at":"2015-08-01T15:59:22Z","body":"I'm just talking theory here, I can't test it right now, and as I said, I'm not sure how the various DBMS handle that :) Just had a look at the the MySQL docs, they say that you should use the parentheses if you want to order the individual selects, and/or the outer reults\r\n\r\nhttps://dev.mysql.com/doc/refman/5.6/en/union.html\r\n\r\nAs for the error, not sure, maybe things are being handled differently with unions, I just remember that you had to use the outer query when using subqueries in for example joins, selects, etc., but maybe I'm mixing things up.\r\n\r\nUnless ORM wizard @lorenzo chimps in, I'll have a look at this later on when I'm back at my machine."}}