php-crud-api

php-crud-api copied to clipboard

Filtering data in back-end based on authenticated user's role set in MySQL DB

[{"_id":"632b2827a6ecce368d5d97f2","body":">I tried looking for an example from the documentation without success. Is it possible to do this?\r\n\r\nYes sure, the user is loaded in the $_SESSION variable and the authorization handlers can read that and apply some custom logic, based on the user properties.","issue_id":1660149109466,"origin_id":1209622049,"user_origin_id":1288217,"create_time":1660063493,"update_time":1660063511,"id":1663772711848,"updated_at":"2022-09-21T15:05:11.847000Z","created_at":"2022-09-21T15:05:11.847000Z"},{"_id":"632b2827a6ecce368d5d97f3","body":"I'm afraid I am stuck with this, I could really use an example on how the filtering could be done for given example in the authorization handlers. I'm looking for the logic to determine what role a user has in a specific table (based on the userId from `$_SESSION['claims']['user_id']` and then allow the API to return that data based on the set role... I couldn't figure it out with the examples in the doc...","issue_id":1660149109466,"origin_id":1212120618,"user_origin_id":26854315,"create_time":1660230434,"update_time":1660230434,"id":1663772711851,"updated_at":"2022-09-21T15:05:11.851000Z","created_at":"2022-09-21T15:05:11.851000Z"},{"_id":"632b2827a6ecce368d5d97f4","body":"I tried fixing up the layout of the example in the OP. Hopefully someone can guide me in the right direction on how to use the authorization handlers to avoid access to some (complete) table, based on the user's role in another table.","issue_id":1660149109466,"origin_id":1221527841,"user_origin_id":26854315,"create_time":1661081762,"update_time":1661081762,"id":1663772711856,"updated_at":"2022-09-21T15:05:11.856000Z","created_at":"2022-09-21T15:05:11.856000Z"},{"_id":"632b2827a6ecce368d5d97f5","body":"To get only the records owned by a user, the relevant table e.g. \"Articles\" should have a foreign key column containing the user id. \r\n \r\n**Sample schema**\r\n\r\n1. Articles table (Sample table with users per row)\r\n\r\n| id | subject | content | date\\_created | fk\\_owner\\_id |\r\n| -- | -------------- | ------------------------------------------------------------- | ------------- | -------- |\r\n| 1 | Welcome | Welcome to Php | 9\/5\/2022 | 1 |\r\n| 2 | Sample | consectetur adipiscing elit, sed do eiusmod tempor incididunt | 9\/7\/2022 | 2 |\r\n| 3 | Another Sample | Lorem ipsum dolor sit amet, | 9\/9\/2022 | 2 |\r\n\r\nHere, the fk_owner_id is the id from the users table.\r\n\r\n\r\n2. Users table\r\n\r\n| id | username | password | fk\\_role\\_id |\r\n| -- | -------- | -------- | ------------ |\r\n| 1 | neo | ********** | 1 |\r\n| 2 | smith |**********| 2 | |\r\n\r\n*Note: For this schema, the user is not yet activated if its fk_role_id is null or zero.*\r\n\r\n3. Roles table\r\n\r\n| role\\_id | rolename | can\\_insert | can\\_update | can\\_delete | is\\_admin |\r\n| -------- | -------- | ----------- | ----------- | ----------- | --------- |\r\n| 1 | admin | 1 | 1 | 1 | 1 |\r\n| 2 | encoder | 1 | 0 | 0 | 0 |\r\n\r\nYou can create a view joining the 2 tables as \r\n\r\n CREATE VIEW valid_users AS\r\n SELECT id,username,password,\r\n fk_role_id, can_insert,can_update,can_delete,is_admin\r\n FROM users\r\n LEFT JOIN roles ON users.fk_role_id = roles.role_id\r\n WHERE fk_role_id > 0;\r\n\r\nthen set the `'dbAuth.loginTable'=>valid_users.`\r\n\r\nSuccessful login will return these selected columns, excluding the password field. These data is accessible via $_SESSION ['user']['columnName']\r\n\r\nYou can then add the recordHandler to filter only the articles owned by the currently logged-in user.\r\n\r\n 'authorization.recordHandler'=>function($operaton,$tableName){\r\n \t\t \t if($tableName === 'articles')\r\n \t\t\t\treturn ($tableName == 'articles') ? 'filter=fk_owner_id,eq,'.$_SESSION['user']['user_id']: '';\r\n \t\t\t\r\n \t\t},\r\n \r\nYou may also adapt the 'authorization.columnHandler' to restrict the returned column,\r\n ","issue_id":1660149109466,"origin_id":1241481011,"user_origin_id":32501234,"create_time":1662697132,"update_time":1662714717,"id":1663772711865,"updated_at":"2022-09-21T15:05:11.865000Z","created_at":"2022-09-21T15:05:11.865000Z"},{"_id":"632b2827a6ecce368d5d97f6","body":"Thank you @apps-caraga for helping @NorthFred. Great example.","issue_id":1660149109466,"origin_id":1241525591,"user_origin_id":1288217,"create_time":1662702536,"update_time":1662702536,"id":1663772711868,"updated_at":"2022-09-21T15:05:11.867000Z","created_at":"2022-09-21T15:05:11.867000Z"},{"_id":"632b2827a6ecce368d5d97f7","body":"@apps-caraga Thank you for the elaborate example! It gives a good idea on how it can be handled. I should have probably mentioned that my solution is using Firebase login to provide jwt auth tokens. So I don't have a 'login' table with username and password as such.\r\n\r\nThe users table only stores the user's `userId` and `role` (and some other information - but it is irrelevant for the solution I seek).\r\n\r\nThe Firebase auth token only contains the userId via `$_SESSION['claims']['user_id']`. Now I would need to find a way to look up the user's role from the 'users' table in order to limit the data access via the `authorization.recordHandler`.","issue_id":1660149109466,"origin_id":1242264285,"user_origin_id":26854315,"create_time":1662744573,"update_time":1662744623,"id":1663772711870,"updated_at":"2022-09-21T15:05:11.870000Z","created_at":"2022-09-21T15:05:11.870000Z"},{"_id":"632b2827a6ecce368d5d97f8","body":"@mevdschee @apps-caraga I am currently looking into getting rid of authentication via Firebase auth tokens and use the `dbAuth` middleware instead. That seems to support my needs better. I am running into a bunch of issues with `dbAuth`, but will create a new issue for that.","issue_id":1660149109466,"origin_id":1242970871,"user_origin_id":26854315,"create_time":1662904609,"update_time":1662904609,"id":1663772711874,"updated_at":"2022-09-21T15:05:11.874000Z","created_at":"2022-09-21T15:05:11.874000Z"},{"_id":"632b2827a6ecce368d5d97f9","body":"So I just got a firebase auth working and yup, the $_SESSION['claims'] variable contains several pieces of user data. For our purpose, the user_id , and the email seems to be the most useful. In this case, it may be possible to adapt the sample Articles table so that the **fk_owner_id** is equal to the **user_id** or the **email** retrieved from the firebase access token.\r\n\r\n```\r\n'authorization.recordHandler'=>function($operaton,$tableName){\r\nif($tableName === 'articles')\r\n\treturn ($tableName == 'articles') ? 'filter=fk_owner_id,eq,'.$_SESSION['claims']['user_id']: ''; \/\/ or $_SESSION['claims']['email'] ;if using the email as the owner identifier\r\n\t\t\t\r\n\t\t},\r\n```\r\n\r\n\r\n\r\n","issue_id":1660149109466,"origin_id":1248100847,"user_origin_id":32501234,"create_time":1663248411,"update_time":1663248411,"id":1663772711879,"updated_at":"2022-09-21T15:05:11.878000Z","created_at":"2022-09-21T15:05:11.878000Z"},{"_id":"637b1d5a4b97542c9a2bb73b","body":"@mevdschee I believe this issue can be closed. I moved the project away from Firebase auth and switched to dbAuth completely - and it seems to be working well so far. I'm only missing a password reset functionality, but that is a different story.","issue_id":1660149109466,"origin_id":1279002895,"user_origin_id":26854315,"create_time":1665753714,"update_time":1665753714,"id":1669012826259,"updated_at":"2022-11-21T06:40:26.259000Z","created_at":"2022-11-21T06:40:26.259000Z"},{"_id":"637b1d5a4b97542c9a2bb73c","body":"@NorthFred Thank you for reporting your success and @apps-caraga thank you for your great example!","issue_id":1660149109466,"origin_id":1279670178,"user_origin_id":1288217,"create_time":1665813588,"update_time":1665813588,"id":1669012826263,"updated_at":"2022-11-21T06:40:26.263000Z","created_at":"2022-11-21T06:40:26.263000Z"},{"_id":"661c498c23eede2dcf05bbe3","body":"Just in case someone else looking for this where have firebase is a must, in my case I created a middleware using ```kreait\/firebase-php``` to verify the jwt token.\r\n\r\nAs mentioned before, there must be a \"users\" table that will have the firebase user id in order to do the lookup, to keep it updated i made a custom user creation flow where after creating the user in firebase a call to custom endpoint is made in order to register the user in my local db.\r\n\r\n```php\r\n class SMN947Middleware extends Middleware\r\n {\r\n private $reflection;\r\n private $db;\r\n private $ordering;\r\n private $debug;\r\n private $firebase;\r\n private $auth;\r\n private $pathHandler;\r\n\r\n public function __construct(Router $router, Responder $responder, Config $config, string $middleware,ReflectionService $reflection, GenericDB $db)\r\n {\r\n parent::__construct($router, $responder, $config, $middleware);\r\n\r\n $this->reflection = $reflection;\r\n $this->db = $db;\r\n $this->ordering = new OrderingInfo();\r\n $this->debug = $config->getDebug();\r\n $firebase = (new Factory)->withServiceAccount('.\/path\/to\/your\/firebase\/file.json');\r\n $this->firebase = $firebase->createAuth();\r\n $auth = $firebase->createAuth();\r\n $this->auth = $auth;\r\n\r\n $this->pathHandler = new pathHandler($this->reflection, $this->db, $this->ordering, $this->auth, $this->responder);\r\n }\r\n\r\n private function getAuthorizationToken(ServerRequestInterface $request): string | null\r\n {\r\n $token = $request->getHeader('X-Authorization')[0] ?? null;\r\n if($token == null) {\r\n return null;\r\n }else{\r\n $parts = explode(' ', trim($token), 2);\r\n if (count($parts) != 2) {\r\n return '';\r\n }\r\n if ($parts[0] != 'Bearer') {\r\n return '';\r\n }\r\n return $parts[1];\r\n }\r\n }\r\n\r\n public function process(ServerRequestInterface $request, RequestHandlerInterface $next): ResponseInterface\r\n {\r\n $token = $this->getAuthorizationToken($request);\r\n\r\n if($token == null) {\r\n $response = $this->responder->error(ErrorCode::AUTHENTICATION_REQUIRED, '');\r\n return $response;\r\n }else{\r\n try {\r\n $verifiedIdToken = $this->auth->verifyIdToken($token);\r\n $uid = $verifiedIdToken->claims()->get('sub');\r\n $user = $this->auth->getUser($uid);\r\n\r\n $requestedPath = $request->getUri()->getPath();\r\n \r\n $authenticatedUser = $this->getAuthenticatedUser($uid);\r\n\r\n if (count($authenticatedUser) < 1) {\r\n return $this->responder->error(ErrorCode::AUTHENTICATION_FAILED, $user->email);\r\n }\r\n\r\n $result = array(\r\n \"status\" => \"OK\",\r\n \"user\" => array(\r\n \"uid\" => $user->uid,\r\n \"email\" => $user->email,\r\n \"displayName\" => $authenticatedUser[0][\"name\"],\r\n \"phone\" => $authenticatedUser[0][\"phone\"],\r\n \"rol\" => $authenticatedUser[0][\"rol\"],\r\n \"storeId\" => $authenticatedUser[0][\"storeId\"],\r\n \"vehicleId\" => $authenticatedUser[0][\"vehicleId\"],\r\n \"token\"=> $token\r\n )\r\n );\r\n\r\n $response = ResponseFactory::fromStatus(ResponseFactory::OK);\r\n $response = $response->withHeader('Content-Type', 'application\/json; charset=utf-8');\r\n $response = $response->withHeader('Content-Length', strlen(json_encode($result)));\r\n $response->getBody()->write(json_encode($result));\r\n $response = $this->responder->success($result);\r\n\r\n return $response;\r\n\r\n } catch (FailedToVerifyToken $e) {\r\n $response = $this->responder->error(ErrorCode::AUTHENTICATION_FAILED, $e->getMessage()); \r\n }\r\n }\r\n return $response;\r\n }\r\n }\r\n```","issue_id":1660149109466,"origin_id":1646903338,"user_origin_id":53320639,"create_time":1690134418,"update_time":1690134418,"id":1713129868947,"updated_at":"2024-04-14T21:24:28.947000Z","created_at":"2024-04-14T21:24:28.947000Z"}]

My latest project uses Firebase authentication for token issuing. The token doesn't contain any information regarding e.g. user's role or permissions. The MySQL database however contains a table 'Users' which...

NorthFred

Suggestion: Make (middleware) config case-agnostic

[{"_id":"632b27dda6ecce368d5d97e4","body":"Good issue. Might be better indeed, since environment parameters are not case sensitive either.","issue_id":1660149109469,"origin_id":1206710757,"user_origin_id":1288217,"create_time":1659722498,"update_time":1659722498,"id":1663772637852,"updated_at":"2022-09-21T15:03:57.852000Z","created_at":"2022-09-21T15:03:57.852000Z"},{"_id":"632b27dda6ecce368d5d97e5","body":"I was thinking that maybe we should convert all config from 'camelCase' to 'snake_case', before making them case-insensitive. Maybe we can detect mixed case and convert (for legacy naming).","issue_id":1660149109469,"origin_id":1208615330,"user_origin_id":1288217,"create_time":1659993134,"update_time":1659993134,"id":1663772637858,"updated_at":"2022-09-21T15:03:57.857000Z","created_at":"2022-09-21T15:03:57.857000Z"},{"_id":"632b27dda6ecce368d5d97e6","body":"Thinking in a different direction: we could give appropriate error messages before executing the script for wrong case properties, so that they wont fail silently.","issue_id":1660149109469,"origin_id":1208619804,"user_origin_id":1288217,"create_time":1659993456,"update_time":1659993456,"id":1663772637867,"updated_at":"2022-09-21T15:03:57.867000Z","created_at":"2022-09-21T15:03:57.867000Z"},{"_id":"632b27dda6ecce368d5d97e7","body":"I don't really have an opinion on which option I prefer, but everything is better than failing silently \ud83d\ude05 \r\nHowever, while error messages can point the user in the right direction, the user then still needs to update the configuration and reupload the api to the server.\r\nIf this is only caused by a case-inconsistency, it can be quite annoying, but could be easily avoided.\r\n\r\nI think adding the error messages should be done in either case, but it should not be the only thing changed \ud83d\ude42\r\n","issue_id":1660149109469,"origin_id":1209080266,"user_origin_id":9429273,"create_time":1660033977,"update_time":1660033977,"id":1663772637876,"updated_at":"2022-09-21T15:03:57.876000Z","created_at":"2022-09-21T15:03:57.876000Z"},{"_id":"632b27dda6ecce368d5d97e8","body":"I guess I slightly prefer my original proposal of keeping it camel-case but ignoring the case in addition to adding error messages, because I think it might be less work than switching to snake_case and it doesn't introduce a breaking change \/ the need for legacy support...","issue_id":1660149109469,"origin_id":1209083442,"user_origin_id":9429273,"create_time":1660034166,"update_time":1660034166,"id":1663772637880,"updated_at":"2022-09-21T15:03:57.880000Z","created_at":"2022-09-21T15:03:57.880000Z"}]

There's probably no particular reason that middleware and other config options are case-sensitive. I think it would be easier for inexperienced users, if this was made case-insensitive, as it might...

nkappler

DbAuthMiddleware not working

[{"_id":"632b2613a49e0e1da50be5d3","body":">i think it gives this error because realName is empty\r\n\r\nI think you may need to have the table 'users' in the 'tables' property. Can you try that?","issue_id":1660149109472,"origin_id":1167298645,"user_origin_id":1288217,"create_time":1656333326,"update_time":1656333326,"id":1663772179059,"updated_at":"2022-09-21T14:56:19.059000Z","created_at":"2022-09-21T14:56:19.059000Z"},{"_id":"632b2613a49e0e1da50be5d4","body":"no it doesn't work again\r\nif i remove \"authorization.tableHandler\" function it works","issue_id":1660149109472,"origin_id":1172702929,"user_origin_id":17611170,"create_time":1656708538,"update_time":1656708538,"id":1663772179063,"updated_at":"2022-09-21T14:56:19.063000Z","created_at":"2022-09-21T14:56:19.063000Z"},{"_id":"632b2613a49e0e1da50be5d6","body":"same here, using \"authorization.tableHandler\" gives 500 internal server error\r\n<img width=\"849\" alt=\"Screenshot 2022-07-08 230147\" src=\"https:\/\/user-images.githubusercontent.com\/23131234\/178041948-cece07de-7e9e-4bd4-bcbe-27721af91a37.png\">\r\n","issue_id":1660149109472,"origin_id":1178955348,"user_origin_id":23131234,"create_time":1657284758,"update_time":1657351602,"id":1663772179068,"updated_at":"2022-09-21T14:56:19.067000Z","created_at":"2022-09-21T14:56:19.067000Z"},{"_id":"637b16c86e9b562f203c04ac","body":"What is the order of your middlewares? Maybe move 'authorization' after the 'dbAuth'? Could that be it?","issue_id":1660149109472,"origin_id":1280013836,"user_origin_id":1288217,"create_time":1665940901,"update_time":1665940901,"id":1669011144592,"updated_at":"2022-11-21T06:12:24.592000Z","created_at":"2022-11-21T06:12:24.592000Z"},{"_id":"637b16c86e9b562f203c04ad","body":"Do you have an update?","issue_id":1660149109472,"origin_id":1288083958,"user_origin_id":1288217,"create_time":1666521949,"update_time":1666521949,"id":1669011144596,"updated_at":"2022-11-21T06:12:24.596000Z","created_at":"2022-11-21T06:12:24.596000Z"},{"_id":"637b16c86e9b562f203c04ae","body":"Closing due to inactivity.","issue_id":1660149109472,"origin_id":1291537575,"user_origin_id":1288217,"create_time":1666763516,"update_time":1666763516,"id":1669011144599,"updated_at":"2022-11-21T06:12:24.598000Z","created_at":"2022-11-21T06:12:24.598000Z"}]

my users table name: users config: tables' => 'product,manufacturer,category', blocking acces // Authorization 'authorization.tableHandler' => function ($operation, $tableName) { return $tableName != 'users'; }, and response {"code":9999,"message":"SQLSTATE[42000]: Syntax error or...

emrahamet

API for uploading files (of any type)

[{"_id":"661c40b723eede2dcf05bab2","body":"i got 404 not found error","issue_id":1660149109475,"origin_id":1513925957,"user_origin_id":586170,"create_time":1681862433,"update_time":1681862433,"id":1713127607552,"updated_at":"2024-04-14T20:46:47.552000Z","created_at":"2024-04-14T20:46:47.552000Z"},{"_id":"661c40b723eede2dcf05bab3","body":"I have 404 as well - somewhere in this block:\r\n\r\n```php\r\nif(!isset($_FILES[$name_in_files])) return $this->responder->error(1003, $name_in_files,['message' => \"filename should be 'file'\"]);\r\n elseif($_FILES[$name_in_files]['error']!=0) return $this->responder->error(1008, $name_in_files,['message' => \"error saving file\"]); \r\n elseif(file_exists($path.$_FILES[$name_in_files]['name'])) return $this->responder->error(1009, '',['message' => \"filename duplicated : \".$_FILES[$name_in_files]['name']]);\r\n \r\n else{\r\n if(!is_dir($path)) mkdir($path);\r\n $name = $_FILES[$name_in_files]['name'];\r\n\r\n move_uploaded_file($_FILES[$name_in_files]['tmp_name'],$path.$name);\r\n return $this->responder->success(['message' => \"file saved successfully\"]);\r\n }\r\n```\r\n\r\nWhen I uncomment, there is no more error 404.\r\nSuccess messages are properly given to frontend.\r\n\r\nError messages are shown always as 404 or 500.","issue_id":1660149109475,"origin_id":1764978540,"user_origin_id":19615586,"create_time":1697478498,"update_time":1697478498,"id":1713127607557,"updated_at":"2024-04-14T20:46:47.557000Z","created_at":"2024-04-14T20:46:47.557000Z"},{"_id":"661c40b723eede2dcf05bab4","body":"Basically, already this line results in error 404 return:\r\n\r\n```php\r\nif(!isset($_FILES[$name_in_files])) return $this->responder->error(1003, $name_in_files,['message' => \"filename should be 'file'\"]);\r\n````\r\n\r\nThis line results in a proper response:\r\n\r\n```php\r\nreturn $this->responder->success(['message' => \"file saved successfully\"]);\r\n```\r\n\r\nDoes anybody have a clue or solution? ","issue_id":1660149109475,"origin_id":1764984086,"user_origin_id":19615586,"create_time":1697478664,"update_time":1697479016,"id":1713127607560,"updated_at":"2024-04-14T20:46:47.560000Z","created_at":"2024-04-14T20:46:47.560000Z"}]

I want to share my custom controller to add the api to upload files of any type. you can use this API `http://localhost:8080/api.php/upload` with the POST method to send your...

imadBelaroussi

Add function support in SELECT query

[]

Add function support in SELECT query: SELECT COUNT(*) AS C FROM ATABLE https://127.0.0.1/api.php/records/mytest/?include=count(*)%20AS%20c

huangyifu

Support for oracle database?

[{"_id":"632b2613d7e37411a8319504","body":"Thank you for your compliments. Oracle database is an expensive paid product. Are you willing to contribute to the effort of building support for it? If you do, then please send me an email.","issue_id":1660149109480,"origin_id":435361510,"user_origin_id":1288217,"create_time":1541161452,"update_time":1541161655,"id":1663772179185,"updated_at":"2022-09-21T14:56:19.184000Z","created_at":"2022-09-21T14:56:19.184000Z"},{"_id":"632b2613d7e37411a8319505","body":"Hi, can you outline the effort that would be required to write an oracle driver ? Thanks","issue_id":1660149109480,"origin_id":583274050,"user_origin_id":8254819,"create_time":1581061772,"update_time":1581061772,"id":1663772179189,"updated_at":"2022-09-21T14:56:19.189000Z","created_at":"2022-09-21T14:56:19.189000Z"},{"_id":"632b2613d7e37411a8319506","body":"I looked into installing Oracle 18c XE in the CentOS 8 docker instance. It requires binaries from:\r\n\r\nhttps:\/\/www.oracle.com\/database\/technologies\/xe-downloads.html\r\n\r\nInstall files are several gigabytes. They seem to be designed for Oracle Linux 7.","issue_id":1660149109480,"origin_id":762062538,"user_origin_id":1288217,"create_time":1610957012,"update_time":1610957031,"id":1663772179192,"updated_at":"2022-09-21T14:56:19.192000Z","created_at":"2022-09-21T14:56:19.192000Z"},{"_id":"632b2613d7e37411a8319507","body":"I tried installing Oracle 18c on CentOS 7 in a docker container. It installs fine, but now I need to configure it. Does anyone have any experience in (automatic) installation and configuration of Oracle?","issue_id":1660149109480,"origin_id":766316518,"user_origin_id":1288217,"create_time":1611479970,"update_time":1611480270,"id":1663772179195,"updated_at":"2022-09-21T14:56:19.194000Z","created_at":"2022-09-21T14:56:19.194000Z"},{"_id":"632b2613d7e37411a8319508","body":"@simevo We need to:\r\n\r\n- have automatic installation of oracle in a docker container\r\n- port the fixtures to the oracle sql variant\r\n- adjust the driver to the oracle sql variant\r\n\r\nI'm stuck at the first step. Help with any of these steps is appreciated.","issue_id":1660149109480,"origin_id":766316882,"user_origin_id":1288217,"create_time":1611480164,"update_time":1611480715,"id":1663772179197,"updated_at":"2022-09-21T14:56:19.197000Z","created_at":"2022-09-21T14:56:19.197000Z"},{"_id":"632b2613d7e37411a8319509","body":"I know this is a low priority issue, but here you can find a docker xe install for testing:\r\nhttps:\/\/hub.docker.com\/r\/gvenzl\/oracle-xe\r\n","issue_id":1660149109480,"origin_id":1114620001,"user_origin_id":6727358,"create_time":1651480343,"update_time":1651480343,"id":1663772179201,"updated_at":"2022-09-21T14:56:19.200000Z","created_at":"2022-09-21T14:56:19.200000Z"},{"_id":"632b2613d7e37411a831950a","body":">I know this is a low priority issue, but here you can find a docker xe install for testing:\r\n\r\nThat is a great docker instance, used by jOOQ, which tells me a lot. I will have to adjust the test script to be able to use it, but that can be done.","issue_id":1660149109480,"origin_id":1114646257,"user_origin_id":1288217,"create_time":1651482528,"update_time":1651482528,"id":1663772179203,"updated_at":"2022-09-21T14:56:19.203000Z","created_at":"2022-09-21T14:56:19.203000Z"}]

I am very happy to see such a good product, I want to know when your code can support the oracle database.

309525902

Add email verification and password reset to dbAuth

[{"_id":"632b24dcd7e37411a83194ae","body":"> in the dbAuth middleware, do you think it would be possible to add the email verification and password reset process?\r\n\r\nCertainly possible and an important feature, but quite a lot of code. I'm marking it an enhancement for picking it up later.","issue_id":1660149109482,"origin_id":1081437793,"user_origin_id":1288217,"create_time":1648533817,"update_time":1648533882,"id":1663771868041,"updated_at":"2022-09-21T14:51:08.040000Z","created_at":"2022-09-21T14:51:08.040000Z"},{"_id":"632b24dcd7e37411a83194af","body":"> smtp server and port (to send both verification and password reset emails)\r\n\r\nWhy not use the mail() function of PHP?","issue_id":1660149109482,"origin_id":1114515773,"user_origin_id":55852574,"create_time":1651470108,"update_time":1651480938,"id":1663771868045,"updated_at":"2022-09-21T14:51:08.044000Z","created_at":"2022-09-21T14:51:08.044000Z"},{"_id":"632b24dcd7e37411a83194b0","body":"I've quite struggled with php mail function, ended up using phpmailer instead","issue_id":1660149109482,"origin_id":1241424313,"user_origin_id":28157038,"create_time":1662690116,"update_time":1662690116,"id":1663771868051,"updated_at":"2022-09-21T14:51:08.050000Z","created_at":"2022-09-21T14:51:08.050000Z"},{"_id":"637b191c33d90d275c3cd089","body":"@nik2208 Do you have an example how the \"password reset\" can be implemented?","issue_id":1660149109482,"origin_id":1278996881,"user_origin_id":26854315,"create_time":1665753388,"update_time":1665753388,"id":1669011740010,"updated_at":"2022-11-21T06:22:20.010000Z","created_at":"2022-11-21T06:22:20.010000Z"},{"_id":"637b191c33d90d275c3cd08a","body":"@NorthFred what do u mean? there's already the password endpoint (different from register)","issue_id":1660149109482,"origin_id":1279023087,"user_origin_id":28157038,"create_time":1665754712,"update_time":1665754723,"id":1669011740013,"updated_at":"2022-11-21T06:22:20.013000Z","created_at":"2022-11-21T06:22:20.013000Z"},{"_id":"637b191c33d90d275c3cd08b","body":"@nik2208 I was referring to the OP's topic of resetting the password in case the user doesn't remember it (e.g. password reset link...). Did you make this work with phpmailer?","issue_id":1660149109482,"origin_id":1279035046,"user_origin_id":26854315,"create_time":1665755285,"update_time":1665755285,"id":1669011740016,"updated_at":"2022-11-21T06:22:20.016000Z","created_at":"2022-11-21T06:22:20.016000Z"},{"_id":"637b191c33d90d275c3cd08c","body":"actually I meant, instead of mail() as suggested by Kolial I used phpmailer.\r\nI didn use it to send password reset instructions, I just said I had troubles making mail() work.\r\n\r\nthere much out there explaining how to use phpmailer.\r\nreguarding the implementation, I've created an endpoint which actually send the email and called it from the fronted when needed","issue_id":1660149109482,"origin_id":1279098678,"user_origin_id":28157038,"create_time":1665758291,"update_time":1665758291,"id":1669011740019,"updated_at":"2022-11-21T06:22:20.019000Z","created_at":"2022-11-21T06:22:20.019000Z"},{"_id":"637b191c33d90d275c3cd08d","body":"@nik2208 Right, thanks for clarifying. I misunderstood your reply. I'll do some more research on topic for password reset implementation.","issue_id":1660149109482,"origin_id":1279107931,"user_origin_id":26854315,"create_time":1665758757,"update_time":1665758757,"id":1669011740021,"updated_at":"2022-11-21T06:22:20.021000Z","created_at":"2022-11-21T06:22:20.021000Z"},{"_id":"637b191c33d90d275c3cd08e","body":"what kind of help do u need?","issue_id":1660149109482,"origin_id":1279109002,"user_origin_id":28157038,"create_time":1665758816,"update_time":1665758816,"id":1669011740024,"updated_at":"2022-11-21T06:22:20.023000Z","created_at":"2022-11-21T06:22:20.023000Z"},{"_id":"637b191c33d90d275c3cd08f","body":"@nik2208 Well, I'm using the 'dbAuth' middleware of this awesome library for login, logout, registration (signup) and password change. What I am missing is a way to let the user reset their password if they don't remember their login details - i.e. they can no longer log in. A typical approach to resolve this, is having the back-end send a \"password reset\" link, via which the user can access a form to reset the password.\r\n\r\nI'm more of a front-end (Angular) person than a back-end dev, so any help is greatly appreciated! ","issue_id":1660149109482,"origin_id":1279176955,"user_origin_id":26854315,"create_time":1665762504,"update_time":1665762504,"id":1669011740026,"updated_at":"2022-11-21T06:22:20.026000Z","created_at":"2022-11-21T06:22:20.026000Z"},{"_id":"637b191c33d90d275c3cd090","body":"@NorthFred have a look [here](https:\/\/github.com\/bartosz-io\/budget-node\/tree\/94959117e718ab498e5e720769b2066d082e17c0). It's actually pretty advanced angular.\r\nI've taken it as an example to implement my auth provider (using php-crud-api with dbAuth authentication) redirecting to my apps on successful login (using php-crud-api in jwt auth mode).","issue_id":1660149109482,"origin_id":1279713515,"user_origin_id":28157038,"create_time":1665829132,"update_time":1665829132,"id":1669011740029,"updated_at":"2022-11-21T06:22:20.028000Z","created_at":"2022-11-21T06:22:20.028000Z"},{"_id":"661c4fab23eede2dcf05bc85","body":"This is an enhancement that I needed, but there are some considerations about using phpmailer and doubling the filesize of api.php.\r\nI've tried to do it anyway and requiring the files inside the namespace. Don't know if it is correct, I'm not experienced.\r\n#1006\r\n\r\nPassword reset is not yet implemented, but could be done using the same principle, via a confirmation email.\r\n","issue_id":1660149109482,"origin_id":1856394951,"user_origin_id":6873524,"create_time":1702579392,"update_time":1702602717,"id":1713131435792,"updated_at":"2024-04-14T21:50:35.792000Z","created_at":"2024-04-14T21:50:35.792000Z"},{"_id":"661c4fab23eede2dcf05bc86","body":"I know this is an old topic, just sharing some thoughts on this. Essentially, the PHP-CRUD-API works as it is. Now for emailing, integrating phpmailer seems to add unnecessary bloat and the library would become a REST + Email API. \n\nAnyway, my idea is for the library to have some kind of event that can trigger async actions, thus enabling suppport for event-driven architecture. \nFor example, upon successful registration, a USER_CREATED event will be emitted and an action such as sending activation email can be triggered and handled by a separate email sending API. ","issue_id":1660149109482,"origin_id":1890846369,"user_origin_id":32501234,"create_time":1705207526,"update_time":1705217259,"id":1713131435797,"updated_at":"2024-04-14T21:50:35.797000Z","created_at":"2024-04-14T21:50:35.797000Z"}]

Hi, in the dbAuth middleware, do you think it would be possible to add the email verification and password reset process? Before a user is effectively registered, he must click...

faFrafa

Support foreign key that points to a non-primary (unique) key

[{"_id":"632b27e4d7e37411a8319555","body":"So, you have a foreign key that points to a non-primary key, am I correct? I never took that situation into account.","issue_id":1660149109484,"origin_id":1008762948,"user_origin_id":1288217,"create_time":1641812650,"update_time":1641812650,"id":1663772644038,"updated_at":"2022-09-21T15:04:04.038000Z","created_at":"2022-09-21T15:04:04.038000Z"},{"_id":"632b27e4d7e37411a8319556","body":"Exactly.. since nosql databases use uuid as a standard, I'm setting things up to make it support (eventually) hybridisation with ease. ","issue_id":1660149109484,"origin_id":1008848989,"user_origin_id":28157038,"create_time":1641819468,"update_time":1641819468,"id":1663772644042,"updated_at":"2022-09-21T15:04:04.042000Z","created_at":"2022-09-21T15:04:04.042000Z"},{"_id":"632b27e4d7e37411a8319557","body":"now that I practiced with simple things, show me the path to work this out ;)","issue_id":1660149109484,"origin_id":1042780662,"user_origin_id":28157038,"create_time":1645092554,"update_time":1645092554,"id":1663772644046,"updated_at":"2022-09-21T15:04:04.046000Z","created_at":"2022-09-21T15:04:04.046000Z"}]

I have a `questions` table and an `answers` table. the `answers` table has a foreignKey field named `questionUuid` related to the field `uuid` (not related to the id field) of...

nik2208

MIDDLEWARE - dbAuth - PHPSESSID Cookie - SameSite Option

[{"_id":"632b2578d7e37411a83194cd","body":"I've added this:\r\nsession_set_cookie_params(['samesite' => 'None', 'secure' => true]);\r\nat line 7864.. just before start_session();\r\nthis seems to do the trick..\r\n\r\nthere are other start_session() on other middleware (maybe 3 or so?) \r\n\r\ncould be nice to have a config option for that ;) ","issue_id":1660149109486,"origin_id":1002559537,"user_origin_id":28157038,"create_time":1640779044,"update_time":1640779044,"id":1663772024365,"updated_at":"2022-09-21T14:53:44.365000Z","created_at":"2022-09-21T14:53:44.365000Z"},{"_id":"632b2578d7e37411a83194ce","body":"Related: https:\/\/github.com\/mevdschee\/php-crud-api\/issues\/827#issuecomment-1012048398","issue_id":1660149109486,"origin_id":1012100598,"user_origin_id":1288217,"create_time":1642077614,"update_time":1642077639,"id":1663772024378,"updated_at":"2022-09-21T14:53:44.377000Z","created_at":"2022-09-21T14:53:44.377000Z"},{"_id":"632b2578d7e37411a83194cf","body":"what about this approach?\r\n```\r\nif (session_status() == PHP_SESSION_NONE) {\r\n if (!headers_sent()) {\r\n $sessionName = $this->getProperty('sessionName', '');\r\n if ($sessionName) {\r\n session_name($sessionName);\r\n }\r\n if(isset($body->allowSameSite)) {\r\n session_set_cookie_params(['samesite' => 'None', 'secure' => true]);\r\n }\r\n session_start();\r\n }\r\n }\r\n```","issue_id":1660149109486,"origin_id":1042743715,"user_origin_id":28157038,"create_time":1645090218,"update_time":1645090218,"id":1663772024384,"updated_at":"2022-09-21T14:53:44.383000Z","created_at":"2022-09-21T14:53:44.383000Z"},{"_id":"637b288a0702b96a30805532","body":"has this feature been merged?","issue_id":1660149109486,"origin_id":1279954434,"user_origin_id":28157038,"create_time":1665921385,"update_time":1665921385,"id":1669015690821,"updated_at":"2022-11-21T07:28:10.820000Z","created_at":"2022-11-21T07:28:10.820000Z"},{"_id":"637b288a0702b96a30805533","body":">has this feature been merged?\r\n\r\nNo, as I still have some doubts about the code.\r\n\r\nWhat is `$body->allowSameSite` do? Also, this syntax for `session_set_cookie_params` is not supported in PHP < 7.3.\r\n\r\nWhy do we need `SameSite` set to `None`, is `Lax` not good enough? \r\n\r\nHow about this code instead?\r\n\r\n if (!ini_get('session.cookie_samesite')) {\r\n ini_set('session.cookie_samesite', $this->getProperty('sameSite', 'Lax'));\r\n }\r\n if (!ini_get('session.cookie_httponly')) {\r\n ini_set('session.cookie_httponly', 1);\r\n }\r\n if (!ini_get('session.cookie_secure') && isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] != 'off') {\r\n ini_set('session.cookie_secure', 1);\r\n }\r\n\r\nPlease let me know what you think, your input is much appreciated.\r\n\r\n","issue_id":1660149109486,"origin_id":1280011742,"user_origin_id":1288217,"create_time":1665940287,"update_time":1665940458,"id":1669015690824,"updated_at":"2022-11-21T07:28:10.824000Z","created_at":"2022-11-21T07:28:10.824000Z"},{"_id":"637b288a0702b96a30805534","body":"Well actually I don't remember and (considering my php knowledge) I don't Kwon what I did think that code would have done.. Actually I've only texted you to understand if we had moved forward from that point.\r\n\r\nWould u exclude to give tue possibility to disable the same site setting overriding the php.ini config?\r\n\r\nIf u e.g. don't have access to php.ini config and use the api cross site you would find yourself in troubles.\r\n\r\nWe could add a cors setting like cors.disableSameSite to set samesite to none and secure to true.\r\n\r\nDoes it sound? ","issue_id":1660149109486,"origin_id":1280016686,"user_origin_id":28157038,"create_time":1665941748,"update_time":1665942143,"id":1669015690827,"updated_at":"2022-11-21T07:28:10.827000Z","created_at":"2022-11-21T07:28:10.827000Z"},{"_id":"637b288a0702b96a30805535","body":">Actually I only text you to understand if we had moved forward from that point.\r\n\r\nWell, we haven't, since it is not an easy issue. Note that I am not against assuming that people aren't able to configure their PHP correctly (through php.ini or php_flag in .htaccess), so I'm willing to make a change to the code.\r\n\r\n> overriding the php.ini config?\r\n\r\nI guess we should override the php.ini setting right? Or shouldn't we? The ini setting is not set by default. \r\n\r\n> We could add a cors setting like cors.disableSameSite to set samesite to none and secure to true.\r\n\r\nCommunication between subdomains (such as api.yourdomain.com and app.yourdomain.com) is allowed without having to set 'SameSite' to 'None'.\r\n\r\nI'm not entirely sure what the defaults should be. It feel it may be a trade-off between security and convenience.","issue_id":1660149109486,"origin_id":1280019498,"user_origin_id":1288217,"create_time":1665942707,"update_time":1665942956,"id":1669015690830,"updated_at":"2022-11-21T07:28:10.829000Z","created_at":"2022-11-21T07:28:10.829000Z"},{"_id":"637b288a0702b96a30805536","body":"Well actually that solution would just be an escape path in very particular situations, so it should be used carefully and with conscience. Your argumentation is more than reasonable. ","issue_id":1660149109486,"origin_id":1280021746,"user_origin_id":28157038,"create_time":1665943448,"update_time":1665943448,"id":1669015690832,"updated_at":"2022-11-21T07:28:10.831000Z","created_at":"2022-11-21T07:28:10.831000Z"}]

is there a way to set, in the PHPSESSID, the option SameSite to "none"? my devel db is on another machine and the browser defaults its value to SameSite="Lax", blocking...

nik2208

Support for stored procedures

[{"_id":"632b2843a49e0e1da50be62e","body":"+1","issue_id":1660149109487,"origin_id":544178562,"user_origin_id":284547,"create_time":1571506877,"update_time":1571506877,"id":1663772739756,"updated_at":"2022-09-21T15:05:39.755000Z","created_at":"2022-09-21T15:05:39.755000Z"},{"_id":"632b2843a49e0e1da50be630","body":"Is the support for stored procedure added?","issue_id":1660149109487,"origin_id":606165960,"user_origin_id":62904590,"create_time":1585592921,"update_time":1585592921,"id":1663772739760,"updated_at":"2022-09-21T15:05:39.760000Z","created_at":"2022-09-21T15:05:39.760000Z"},{"_id":"632b2843a49e0e1da50be631","body":"Would be really great\ud83d\ude0a\ud83d\udc4f","issue_id":1660149109487,"origin_id":672260799,"user_origin_id":265594,"create_time":1597177464,"update_time":1597177464,"id":1663772739767,"updated_at":"2022-09-21T15:05:39.766000Z","created_at":"2022-09-21T15:05:39.766000Z"},{"_id":"632b2843a49e0e1da50be632","body":"Was wondering how i can introduce custom sql queries ? using this api.\r\nLike in case i wanna run a very complex query with joins and agregation...","issue_id":1660149109487,"origin_id":678396284,"user_origin_id":7417007,"create_time":1598029808,"update_time":1598029808,"id":1663772739770,"updated_at":"2022-09-21T15:05:39.770000Z","created_at":"2022-09-21T15:05:39.770000Z"},{"_id":"632b2843a49e0e1da50be633","body":"> We could support stored procedures or similarly \"soft\" or \"fake\" stored procedures, which are really SQL files on disk that will be executed on an API endpoint.\r\n\r\nYeah, I was also thinking about fake views using the same logic, this could be a way for my previous comment, since views are pretty much a simple SQL query. what do you think ?","issue_id":1660149109487,"origin_id":678403298,"user_origin_id":7417007,"create_time":1598030848,"update_time":1641210494,"id":1663772739773,"updated_at":"2022-09-21T15:05:39.772000Z","created_at":"2022-09-21T15:05:39.772000Z"},{"_id":"632b2843a49e0e1da50be634","body":"> what do you think ?\r\n\r\nI think it would be a great addition to have fake\/soft views and procedures.","issue_id":1660149109487,"origin_id":731013801,"user_origin_id":1288217,"create_time":1605859797,"update_time":1605859797,"id":1663772739776,"updated_at":"2022-09-21T15:05:39.775000Z","created_at":"2022-09-21T15:05:39.775000Z"}]

We could support stored procedures or similarly "soft" or "fake" stored procedures, which are really SQL files on disk that will be executed on an API endpoint.

mevdschee