DEV Community: Abdurrahman Shofy Adianto

How to update multiple rows based on list of key-val pairs (in MySQL, MariaDB, & PostgreSQL)

Abdurrahman Shofy Adianto — Fri, 29 Sep 2023 09:26:50 +0000

I've encountered the needs for this technique multiple times. Mostly when the data is generated from other applications, and my only access to the database is via web-based SQL interface such as Adminer or PHPMyAdmin. It seems that currently there are no comprehensive article outlining how to achieve this. So I'll just write my findings here.

MySql 5.x

as this version doesn't support VALUES clause yet, our only choice is to use the ugly CASE-WHEN syntax:

UPDATE table_name
SET changed_col = CASE comparison_col
           WHEN 'key1' THEN 'value1'
           WHEN 'key2' THEN 'value2'
           ...
        END
WHERE comparison_col in ('key1', 'key2', ...)

reference: https://stackoverflow.com/a/13067614

MySql 8.x

Luckily this version introduced VALUES clauses, so we could write the data more concise using VALUES ROW(..), ROW(..) syntaxes.

UPDATE table_name
SET changed_col = temp_data.column1
FROM (VALUES
  ROW('key1', 'val1'),
  ROW('key2', 'val2'),
  ....
) as temp_data
WHERE comparison_col = temp_data.column0

https://dev.mysql.com/doc/refman/8.0/en/values.html

MariaDB 10.x

MariaDB's VALUES clause is shorter as it doesn't use the ROW keyword at all

UPDATE table_name
SET changed_col = temp_data.column1
FROM (VALUES
  ('key1', 'val1'),
  ('key2', 'val2'),
  ....
) as temp_data
WHERE comparison_col = temp_data.column0

PostgreSQL

Postgres definitely have the best syntax among the three, as it support aliasing column names for VALUES syntax, just as SQL Server did

UPDATE table_name
SET changed_col = temp_data.col_name_1
FROM (VALUES 
        ('key1', 'val1'),
        ('key1', 'val2'),
        ...
) temp_data (col_name_1, col_name_2)
WHERE comparison_col = temp_data.col_name_2

reference: https://stackoverflow.com/a/18799497

Conclusion

Indeed, if you have the data in CSV format, or you have direct access to the database, its easier to just import it using dedicated SQL toolbox. However as this is not always the case, I think this article should be valuable, at least for myself in the future. Hope it helps

How to edit & replace NPM package dependency

Abdurrahman Shofy Adianto — Tue, 15 Aug 2023 12:08:50 +0000

Last week, my first actual contribution into a public open source project has been merged. Its actually only a small changes into Unstorage library, which is used by Nitros server engine, which in turn used by the popular NuxtJs framework. At the time, I was stuck in one of my side project which used Nuxt because Unstorage is lacking some functionalities that I need. However because Unstorage is 2 layers deep in the dependency tree, its not trivial for me to change the code there.

I imagine this is actually a common task when working with NPM. There are many reason for people to changes the library which is several layers deep in the dependency tree. Maybe they encounter a bug that need to be fixed, or there are some security issue so they wish to change the dependency into another library. However at the time I can’t find any complete tutorial for achieving this task. I only found some partial steps or incomplete docs that I need to piece together. So after some fiddling and some trials and errors, I finally found how to do it. Here I will share the methods so other people wouldn’t have to go through the same trouble as I did.

To show you how its done, I will demonstrate by doing basically the same thing. That is by creating a new Nuxt project, and then forking the unstorage package with my own changes. However in this example we would only add a simple change that wouldn’t affect the overall feature, just to illustrate how its done. You could ignore the NuxtJs parts as its not actually required for our main topic here.

Setting up the scene

First lets create a new Nuxtjs Project.



npx nuxi@latest init my-app
cd my-app

then lets setup a simple page to use unstorage feature. Lets create a simple counter page using it that would store into the default unstorage store (which is memory store). first we will edit app.vue file:



// app.vue
<template>
  <div>
    Counter: {{ counter }}
    <button @click="updateCounter()">
      add
    </button>

    <br />
    Server Counter: {{ server_counter }}
  </div>
</template>

<script setup lang="ts">
const counter = useState('counter', () => 0)
const server_counter = useState('server_counter', () => 0)

async function updateCounter() {
  counter.value++

  const res = await $fetch('/api/add_counter', {
    method: 'post', 
    body: { counter: counter.value },
  })

  server_counter.value = res.value
}
</script>

then we will add server/api/add_counter.post.ts. Again, this is nuxtjs stuffs. Its not necessary to understand how to replace NPM dependency.



// server/api/add_counter.post.ts
export default defineEventHandler(async (event) => {
  const { counter } = await readBody(event)

  await useStorage().setItem('data:counter', counter)
  const res = await useStorage().getItem('data:counter')

  return { value: res }
})

now we have a simple dummy example for our demonstration:

Forking the depended package

Lets clone the unstorage package into our local env, then change it to our needs.



git clone https://github.com/unjs/unstorage.git
cd unstorage
npm install

to illustrate our change, lets just add a simple console.log in src/drivers/memory.ts whenever we store new value



    ...
    setItem(key, value) {
      console.log(`setting key ${key} with value ${value}`)
      data.set(key, value);
    },
    ...

now run npm run build to make sure our local changes is ready to use

Overwrite dependency in our main package with local changes

After we finished our local changes, now we are ready to overwrite our dependency. Open package.json of our Nuxt project, then in devDependencies section we’ll overried unstorage package with the local path to our forked unstorage package. Of course this depends on your needs whether to change devDependencies or dependencies section.



  // package.json
  // other settings...
  "devDependencies": {
    "unstorage" : "file:/full/path/to/your/forked-unstorage",
    // other dependencies...

Notice that we use file:<path> format for our dependency location. Now we add a new overrides section to our package json. Here we need to adjust based on our dependency tree. In our case, unstorage is a dependency of Nitros server engine, which in turn is a dependency of NuxtJs. So our overrides section would look like this:



  // package.json
  // other settings...  
  "overrides": {
    "nuxt": {
      "nitropack": {
        "unstorage": "$unstorage"
      }
    }
  }
}

now run npm update our Nuxt folder to update our dependency, then restart our local nuxt server. We could confirm by running ls -al node_modules/unstorage on our Nuxt folder and see that our unstorage folder has been changed into a link to our forked unstorage folder:

now when we try our nuxt app, it would print out our latest changes:

Publishing our changes temporarily

We have successfully override our dependency locally. Now hot to use it in our staging or production environment? We actually need to make our changes available publicly. The best ways is of course to merge it into the official upstream package. You could create a new pull request here to the upstream repo, and the repo maintainer would merge it into the codebase which you could use it in your project. Of course this rarely would happen quickly, so we the alternative is to just publish it in github. NPM actually support using github repo & branch as NPM package, so we would use this method here.

First lets create a new branch in our local folder, than commit the build folder into the new branch. Then push it into your own github repo.



# in our forked unstorage folder
git checkout -b my-build-branch
git add -f dist
git commit 'example npm build'
git push -o origin my-build-branch

You could see my branch in my github account for reference. There you could see that it contains dist folder which contain my local build at the time. In my case, dist folder is actually included in .gitignore of unstorage repo so I need to run git add -f dist to forcefully add the folder into my commit.

Now, we are ready to update our package.json once more. Update our devDepencies / dependecies section with our public github repo & branch. For example, in my case it would be:



  // package.json
  // other settings...
  "devDependencies": {
    "unstorage": "azophy/unstorage#my-build-branch",

then run npm update once more. now you are ready to use your new fork on your project.

Conclusion

I hope this article could give you a clear outline about how to replace NPM dependency by yourself. Feel free to comment if you find anything lacking.

Using Laravel as a service proxy/gateway

Abdurrahman Shofy Adianto — Mon, 20 Feb 2023 06:20:07 +0000

Last week at my dayjob, I was exploring options for implementing a proxy endpoint for one our external service. One of the stack used in our application is Laravel, so naturally I investigated how to implement a proxy endpoint using it. Turns out its pretty simple a Laravel already included most of the stuff required to built it. This approach should be suitable for many simple use case to avoid overhead of adding new dedicated proxy service such as a full blown API gateway like Traefik or Kong, or if you need custom logic which may be hard to achieve using on-the-shelf solutions.

why

When building application using microservice/service oriented architecture, its common to require connection to external services. This services may be some vendor API such as email provider, payment gateway, or simply internal service that one way or another happen to be in a different networks. Some common use cases:

limit access to credential. by using a single proxy service, other services didnt need to store credential to external services, thus providing better control in terms of maintanance & security.
sharing resource. for example: oauth token. each service didn't need to request their own token, but instead could share the same token which handled by the proxy service. other example is to caching resource, so frequently accessed resources only need to be accessed in a handful of times

why laravel?

In restropect, Laravel may be an odd choice for building a custom proxy service/API Gateway. If you intent on making the service as a standalone service, it may make more sense to use some microframework such as Lumen or just use Symphony. Other options is to use other more performant stack like Golang or NodeJs. However in my case, the system would actually be embedded in existing Laravel service (because of some reasons), and because of that I want to share my experience, in case someone encounter similar situation like I did.

pros compared to dedicated proxy service/api gateway

easy to implement custom logic. when using dedicated service, you may need to use some obscure DSL, or plugging your script in some weird way. Obviously this may be subjective, but IMHO using existing stack is a clear advantage as it reduce cognitive load on building & maintaining the service.
no additional maintenance overhead: no new technology need to be learn

cons

additional work hops compared to direct access, although it depends on situation. caching may actually improve network connection
limited scalibility

how

prerequisities

for this tutorial, we'll use Guzzle library as HTTP client. some of you may wondering, "why dont we use Laravel's built-in HTTP client?". Well, Laravel's client is easier to use, but its less flexible for our needs. Laravel's HTTP Client is actually just a wrapper around Guzzle, so it made sense that it may actually less flexible.

To install guzzle just run

composer require guzzlehttp/guzzle

Also for brevity, here we would implement the endpoint right in our route file. In practice, it would be cleaner to separate the logic into dedicated controller file. Here you could choose either routes/web.php or routes/api.php, or event create a new route file if you prefer.

basic usage

Lets start simple. Here we would create new endpoint that would call httpbin.org given the path & HTTP method

use GuzzleHttp\Client as HttpClient;
Route::any('/proxy/{path}', function(Request $req, $path) {
  $client = new HttpClient([
    'base_uri' => 'https://httpbin.org'
  ]);

  return $client->request($req->method(), $path);
});

the code should be pretty straighforward. for every request to /proxy/{path}, it would make a request to httpbin.org/{path} with the same HTTP method. you could try requesting the new endpoint using different methods to see it in effect. here I used HTTPie to test the endpoint:

http POST localhost:8000/proxy/post
http PUT localhost:8000/proxy/put
http DELETE localhost:8000/proxy/delete

proxy-ing subpaths

If you notice, the path variable actually only able to retrieve the path, but not the subpath. for example getting localhost:8000/proxy/get works, but localhost:8000/proxy/get/subpath would failed, because laravel would not be able to route the later. the solution is simply to add 'where" method to allow path variable to catch all subpath. so just add:

Route::any(
  //...
)->where('path', '.*');

adding request body, params & response code

you may also realize that our current implementation doesn't forward our request body, query params, and response status code. so lets change that:

//...
$resp =  $client->request($req->method(), $path, [
  'query' => $req->query(),
  'body' => $req->getContent(),
]);

return response($resp->getBody()->getContents(), $resp->getStatusCode());
//...

forwarding necessary headers

Finally, you may also realize that our implementation does't forward headers of both request & response. Headers are actually pretty tricky as they may affect our request & response and turns them invalid. I personally found that it best to only forward the necessary header fields, and ignore the rest. This also improve our security.

To do this, we will prepare a helper function to filter our headers, amd only extract only 'content-type' & 'accept' headers. of course you could also modify it according to your needs:

// simple helper function to filter header array on request & response
function filterHeaders($headers) {
    $allowedHeaders = ['accept', 'content-type'];

    return array_filter($headers, function($key) use ($allowedHeaders) {
        return in_array(strtolower($key), $allowedHeaders);
    }, ARRAY_FILTER_USE_KEY);
}

and then we could use it in our endpoints. Our final could would be this:

<?php
// you could use either routes/web.php or routes/api.php

// simple helper function to filter header array on request & response
function filterHeaders($headers) {
    $allowedHeaders = ['accept', 'content-type'];

    return array_filter($headers, function($key) use ($allowedHeaders) {
        return in_array(strtolower($key), $allowedHeaders);
    }, ARRAY_FILTER_USE_KEY);
}

Route::any('/proxy_example/{path}', function(Request $request, $path) {
    $client = new GuzzleHttp\Client([
        // Base URI is used with relative requests
        'base_uri' => 'https://pie.dev', // public dummy API for example
        // You can set any number of default request options.
        'timeout'  => 60.0,
        'http_errors' => false, // disable guzzle exception on 4xx or 5xx response code
    ]);

    // create request according to our needs. we could add
    // custom logic such as auth flow, caching mechanism, etc
    $resp = $client->request($request->method(), $path, [
        'headers' => filterHeaders($request->header()),
        'query' => $request->query(),
        'body' => $request->getContent(),
    ]);

    // recreate response object to be passed to actual caller 
    // according to our needs.
    return response($resp->getBody()->getContents(), $resp->getStatusCode())
       ->withHeaders(filterHeaders($resp->getHeaders()));

})->where('path', '.*'); // required to allow $path to catch all sub-path

This implementation should cover 80-90% of most use case. However as stated in the beginning of this article, you may extend this code to include more functionalities. For example you could add authentication mechanism here, or some caching to reduce the number of network request.

Cookies for Authentication are harder than they look

Abdurrahman Shofy Adianto — Fri, 23 Dec 2022 08:20:36 +0000

When you create an HTTP API, one of the most important consideration is: how will we accept access token from client? 2 of the most popular options are utilizing Bearer Token Header, and using HttpOnly Cookie. Both have pros & cons, however I won't discuss it at length here (as its been discussed and debated numerous of times before). I'll just say that in one of my recent work, me and my colleagues has decided that cookies is the most suitable for our needs and agreed to use it for our API.

However after struggling to implement an authentication flow based on it, I've found that implementing a proper cookie-based authentication scheme could be harder than it look. I've also found that the documentation about this is somewhat scattered and partial. Because of it I tried to gather and outline various knowledge I've encountered, and hope that this article could help other unfortunate souls so they don't have to go through the same suffering as I did.

Here I mainly use NestJs as example, but the knowledge should be applicable for other languages or frameworks.

Level 0: cookies with regular settings

Changeable by JavaScript in browsers. May be useful to send persistent (user changeable) data to backend. just make sure to validate the input just like a regular user input. example use case is for saving language setting for website. user could set cookies via javascript, which would be sent for every later requests to the server. Example in NestJs:

// at controller

@Get('/get_cookies')  
getGetCookies(@Req() req: Request): string {
  return req.cookies // get all cookies sent by client/browser
}

@Get('/set_cookies')  
getSetCookies(@Res({ passthrough: true }) res: Response): string {
  res.cookie('cookie_name', 'cookie_value')

  return { status: 'ok'}
}

Level 1: httpOnly cookies, for usage by the same subdomain (usually fullstack app)

"httpOnly" flag allows cookies which is not accessible by browser's javascript, good for storing sensitive info like access token. by default it would be sent at each subsequent non-ajax request, including normal link clicks & form submissions. optionally add "secure" flag. Example in NestJs:

// at controller
@Get('/set_cookies')  
getSetCookies(@Res({ passthrough: true }) res: Response): string {
  res.cookie('cookie_name', 'cookie_value', {
    httpOnly: true,
  })

  return { status: 'ok'}
}

Level 2: Level 1, but called from frontend

In the past the term for this type of request is "Ajax", nowadays people usually using fetch API or custom library like Axios. To send cookies we need to setup frontend with "credentials: include". More information about this 'credentials' setting in MDN. Example using Fetch API:

fetch('https://example.com/endpoint', {
    credentials : 'include',
})
.then(r => { 
    if (r.ok) console('success');
})

Level 3: Level 2, but Frontend & Backend at different subdomains (but still under the same domain)

By default browsers would block these kind of requests because of a security mechanism called CORS (Cross Origin Resource Sharing). This mechanism designed to protect browser users from many kind of attack involving browser requests, including CSRF (Cross-Site Request Forgery), Cookie Hijacking, Session Hijacking, etc. To enable cross-subdomain communication between frontend & backend, and also to protect us from mentioned attacks, we need to set CORS Header settings from backend side of things. In short, we need to configure Cross-Origins-Allow-Credentials and Cross-Origin-Allow-Origin Headers. Again example in NestJs:

// main.ts
const app = await NestFactory.create(AppModule);

app.enableCors({
   // read more options here: https://github.com/expressjs/cors#configuration-options
   origins: [ 
       'https://frontend.example.com',
   ],
   credentials: true,
});

await app.listen(3000);

FYI, we could also set origins to wildcard value (*) but this is discouraged for most kind of public access. This value is also blocked for more advanced usages, for example the SameSite:None cookie flag which we will discuss later. Instead, in some web frameworks we could configure some kind of pattern matching to allow more flexible URL. Of course using 'catch-all' patterns would defeat the purpose, so try to limit our pattern's scope as narrow as possible. Again example in NestJs:

// main.ts
const app = await NestFactory.create(AppModule);

app.enableCors({
   // read more options here: https://github.com/expressjs/cors#configuration-options
   origins: [ 
       // using regex, enable all subdomains of example.com
       /https:\/\/[a-z0-9\.]*example\.com/,
   ],
   credentials: true,
});

await app.listen(3000);

Level 4: Level 3, but BE & FE at different domains

At production environment, this setup is actually discouraged as it opens our website to more attacks (although CORS setting somewhat helps reduce the risk). However this setup is common for development as it allows frontend developers to test remote API from local environment. For production usage, try stick to using the same root domain.

To enable this scenario, we need to add samesite: none & secure: true cookie flag when backend is storing cookies. Example in NestJs:

// at controller
@Get('/set_cookies')  
getSetCookies(@Res({ passthrough: true }) res: Response): string {
  res.cookie('cookie_name', 'cookie_value', {
    httpOnly: true,
    sameSite: "none",
    secure: true,
  })

  return { status: 'ok'}
}

Level 5: Level 4, but mixed between Ajax requests & regular requests

I'm struggling with this. Example use case is passing access token data between BE & FE. Access token is set when user visit BE endpoint, then BE redirect to FE page. Common for some SSO flows, like Laravel Socialite's SSO Flow which utilized browser's redirect. Chrome still support this flow, but currently Firefox doesn't. On Firefox the cookies won't be send to backend from the frontend after redirect.

Fortunately from what I found, this problem is mainly when we use cross-domain request. For cross-subdomain (under the same domain) requests, it seems that there are no problems.

Gotchas

Cookies will only be sent to the same the host with the one set it

even "Domain" cookie flag is considered invalid if its different from requesting host. I've stumbled into a problem about this when I develop local API and publish it with public url using Localhost.run. So when the API set a cookie in browser, I didn't realize that the URL I visit is actually the local one instead of the public one (although its actually the same API). So of course the the cookies couldn't be read because its set & read by different domains/hosts.

Cookies are not 100% temper proof, even on httpOnly & secure settings

As in everything in technology, no matter how advanced the security measures are, in the end its still made by humans with all their flaws. Wikipedia page for HTTP Cookie lists a couple of said vulnerabilities.

References

StackOverflow answer which outline the requirement for cross-domain Cookie storage
https://github.com/benawad/how-to-debug-cookies/blob/master/README.md
How to debug cookies when they dont work (youtube)
Mozilla Developer Docs on Cookies
My proof of concept repo for cross-domain cookie setup using NestJs and Laravel

DEV Community: Abdurrahman Shofy Adianto

How to update multiple rows based on list of key-val pairs (in MySQL, MariaDB, & PostgreSQL)

MySql 5.x

MySql 8.x

MariaDB 10.x

PostgreSQL

Conclusion

How to edit & replace NPM package dependency

Setting up the scene

Forking the depended package

Overwrite dependency in our main package with local changes

Publishing our changes temporarily

Conclusion

Using Laravel as a service proxy/gateway

why

why laravel?

pros compared to dedicated proxy service/api gateway

cons

how

prerequisities

basic usage

proxy-ing subpaths

adding request body, params & response code

forwarding necessary headers

Next

Cookies for Authentication are harder than they look

Level 0: cookies with regular settings

Level 1: httpOnly cookies, for usage by the same subdomain (usually fullstack app)

Level 2: Level 1, but called from frontend

Level 3: Level 2, but Frontend & Backend at different subdomains (but still under the same domain)

Level 4: Level 3, but BE & FE at different domains

Level 5: Level 4, but mixed between Ajax requests & regular requests

Gotchas

Cookies will only be sent to the same the host with the one set it

Cookies are not 100% temper proof, even on httpOnly & secure settings

References