Author: yodi

Categories
Database

Convert epoch time to timestamp in Postgresql

Epoch time stored with bigint format in PostgreSQL. Most of problem when converting this epoch format to date or timestamp is because we deal with time being stored in number format and integer column type.

When I’m trying to convert epoch time that stored in bigint format, I found several way ready on Stackoverflow but didn’t works. Several script like below is not working for the latest PostgreSQL 13 version.

select *, to_timestamp(time in milli sec / 1000) from mytable

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';

SELECT DATE(builds.build_created/ 1000) FROM builds;

You may trying with several approach like to_timestamp, to_date and receive several error results like :

1. Timezone not found
2. Need to cast
3. Or Operator does not exists

Some errors details eg:

Continue reading “Convert epoch time to timestamp in Postgresql”
Categories
Database

Query and get the value from nested JSON in Postgres

Postgresql provides features to do the query on JSON field eg: json_array_elements. Please make your column have JSON format to use these features. Now, often the problem is how to do the query for particular row data that have nested JSON structure inside.

For instance, I have table with structure as below

CREATE TABLE public.raw (
	id bigserial NOT NULL,
	url text NULL,
	response_body json NULL,
	CONSTRAINT raw_pkey PRIMARY KEY (id)
);

And I have data stored in column response_body, with 1 row data:

{
  "response": "Success",
  "data": [
    {
      "cid": "5229",
      "country": "JP",
    },
    {
      "cid": "1002",
      "country": "US",
    },
    {
      "cid": "2004",
      "country": "US",
    },
    {
      "cid": "100",
      "country": "MY",
    },
    {
      "cid": "2999",
      "country": "AG",
    },

Let’s say, I want to show list of “cid”

The problem here, I want to iterate elements in this JSON data and get the values on specific keys. How to do it in Postgresql? All you need just do nested “json_array_elements” (I’m ignoring performance here).

select json_array_elements(response_body->'data')->>'cid' as cid FROM public.raw where id=1

Here are the explanation steps:

  1. Access the data keyname using response_body->'data'
  2. Wrap this with json_array_elements to produce array output
  3. Access the nested key name by using ->> (field name)

If you want to give list of country, then use ->>country. If you want to iterate more, than you can create the nested same as the code above.

Some good references:

https://gist.github.com/sebflipper/e95d12d72492fbab1b08

Categories
Android

Frida Android Make Toast non-rooted Device

I found it’s tough to understand how to just making simple “Toast” in Android via Frida script. Been search to various articles on how to do Toast, but I’ve faced so many errors, for example:

{'type': 'error', 'description': "Error: makeText(): argument types do not match any of:\n\t.overload('android.content.Context', 'int', 'int')\n\t.overload('android.content.Context', 'java.lang.CharSequence', 'int')\n\t.overload('android.content.Context', 'android.os.Looper', 'java.lang.CharSequence', 'int')", 'stack': "Error: makeText(): argument types do not match any of:\n\t.overload('android.content.Context', 'int', 'int')\n\t.overload('android.content.Context', 'java.lang.CharSequence', 'int')\n\t.overload('android.content.Context', 'android.os.Looper', 'java.lang.CharSequence', 'int')\n
   at pe (frida/node_modules/frida-java-bridge/lib/class-factory.js:549)\n    at frida/node_modules/frida-java-bridge/lib/class-factory.js:951\n    at /script1.js:22\n    at frida/node_modules/frida-java-bridge/lib/vm.js:11\n    at frida/node_modules/frida-java-bridge/index.js:304\n    at /script1.js:24", 'fileName': 'frida/node_modules/frida-java-bridge/lib/class-factory.js', 'lineNumber': 549, 'columnNumber': 1}

Fyi, I already know how to do it in Kotlin or Java code directly into the app. Based on error in this code, I make two assumptions:

1. I’m wrong in calling Context
2. I’m wrong in create makeText(args1, args2, args3)

To ensure we have same environment, in this example, I use the latest Frida version on May 2020 on Non-root Android, Windows 10, Execute it via Python connected to remote server via adb tcp forward 5555. I use real device (not emulator)

Here is script that works :

Continue reading “Frida Android Make Toast non-rooted Device”
Categories
Rust

Rust Reqwest via Proxy and SSL Certificate captured by Burp

Make browser or API call requests via Rust script can be done with Reqwest module. It’s easier than using Hyper, which recently has a lot of changes. There nothing many articles, guidelines or working examples with the latest version of Hyper and Reqwest for advanced usage.

Several things that I want to achieve here where there lack of coverage on:

  1. How to enable reqwest working with proxy
  2. How to use Authorization Beaver in reqwest 
  3. How to applied self signed certificate 
  4. How to capture the traffic via Burp for monitoring

I tried with Hyper and Hyper Proxy, but it seems it’s more complicated and harder to do than Reqwest.

Here is an step by step to enable proxy, certification and captured by Burp.

cargo.toml

[dependencies]
hyper = "0.13.5"
hyper-native-tls = "0.3.0"
reqwest = { version = "0.10.4", features = ["blocking", "json", "gzip"] }
tokio = { version = "0.2", features = ["full"] }
diesel = { version = "1.4.4", features = ["postgres", "chrono"] }
dotenv = "0.15.0"
chrono = { version = "0.4.11", features = ["serde"] }
serde = { version = "1.0.110", features = ["derive"] }
Continue reading “Rust Reqwest via Proxy and SSL Certificate captured by Burp”
Categories
Android

Monitor Android network traffic with Burp

We can sniff all traffic that is happening on our Android phone. The idea is by connecting our phone to a proxy that acts as MITM or Middleman. Same as Pi-hole able to showing all log DNS queries, Burp can be acting as proxy software to capture all traffic that comes to it.

Here is how it works from a high-level. Our phone connects to the internet as usual via the router. To monitor the traffic, we shall route our request to a single place, called a proxy server. This proxy will capture and have the ability to intercept the traffic and sending it to the internet. The response from the request is also going into the same channel flow.

Android Phone (Use Proxy’s Cert) —> Proxy —> Internet

How to ensure this solution works for HTTPS, which encrypted with certification? To make this works, we need to inject our certification into the device and make it trusted (There will be step for that). If our own certification acknowledged by the device, then we can decrypt the traffic from the server that issued the certification.

Continue reading “Monitor Android network traffic with Burp”
Categories
Networking

Install Samba Ubuntu 20.04 and Windows 10 Sharing

Here is a step guideline to install Samba in Ubuntu 20.04, Focal Fossa, to enable sharing with Windows 10 as the client. I write this article to help people solving problem around:

1. Windows 10 not connecting properly with Samba 4.7.6 on Ubuntu 18.04
2. Ubuntu 16 Samba server with Windows 10 client – Tutorial/Howto
3. I can’t get Win10 PC to Access Ubuntu 18.10 Samba Shares
4. Cannot access smb share from Windows 10
5. Fixing broken Samba File Share access for Linux hosts after installing the latest version of Windows 10

In this case, I didn’t change Windows features for Samba v1 or change regedit configuration. This is works on pure Windows 10 and Ubuntu 20.04 without additional changes

Here are the steps on “How to install Samba on Ubuntu 20.04 and Fix Sharing with Windows 10”

Continue reading “Install Samba Ubuntu 20.04 and Windows 10 Sharing”
Categories
Networking

Install Pi-hole on Ubuntu 20.04 Laptop via Wifi

After reading about Pihole + Raspberry Pi to filter traffics from DNS level without installing any client-side software, that inspires me to try the same. Several questions that pop-up in my mind in the meantime I’m browsing Raspberry Pi B. I researched all those questions in my mind and found several answers.

Update: https://pi-hole.net/2020/05/10/pi-hole-v5-0-is-here/#page-content

  1. Can we use Pi-hole without Raspberry?
    Yes, we can use Pi-hole without buying raspberry. You can save your money here. Don’t buy Raspberry yet if you have an unused old laptop or old macbook pro / macbook air (with Ubuntu or Linux installed of course)
  2. Can we use Ubuntu Laptop as our Pi-Hole router?
    Definitely, we can. Pi-Hole is software that is acting as DNS provider. Either you installed in Raspberry Pi or Laptop, it’s the same.
  3. Can we use wifi / wireless instead ethernet LAN cable connected to the router?
    Yes. Although on the internet there is several arguments about ethernet cable vs. wireless, I found it there no significant difference. In the beginning, I thought I going to need a laptop with LAN port to install Pi-HOLE.
  4. Do I need to change my router configuration?
    This guide will help you to install PI-Hole without need to change your router configuration. This is my problem earlier because I found that my router being locked and no option to change the DHCP.
  5. Is USB installation required here?
    No need, unless you want to install Raspbian OS rather ubuntu.
Continue reading “Install Pi-hole on Ubuntu 20.04 Laptop via Wifi”
Categories
Mandarin

Fix “UTF8” encoding “WIN1252” CPutf8 PostgreSQL Windows 10

Today I found very weird errors when importing database from Ubuntu to Windows 10 (Chinese language / locale). It’s showing multiple errors start from

locale "English_United States.utf8": codeset is "CPutf8"
Character with encoding UTF8 has no equivalent in WIN1252
ERROR: character with byte sequence 0xe9 0x94 0x99 in encoding "UTF8" has no equivalent in encoding "WIN1252"
character with byte sequence 0xe9 0x94 0x99 in encoding "UTF8" has no equivalent in encoding "WIN1252"

This errors occur because the collate and ctype on current OS have different type than from another database (which is UTF8).

Continue reading “Fix “UTF8” encoding “WIN1252” CPutf8 PostgreSQL Windows 10″
Categories
Elixir

Install Elixir Phoenix Framework in Ubuntu 20.04 Focal Fossa

Here is a quick steps of Phoenix Framework installation in Ubuntu 20.04 – Focal Fossa ready for website development. In summary, the steps will install the Erlang, Elixir, Phoenix Framework and PostgreSQL.

1. Erlang and Elixir Installation
First, lets install Erlang. When the pop-up of codename appear, please input Ubuntu 20.04 Codename which is “Focal”:

sudo dpkg -i erlang-solutions_1.0_all.deb 
sudo apt-get update
sudo apt-get install erlang
sudo apt-get install elixir
sudo apt-get install git vim
Continue reading “Install Elixir Phoenix Framework in Ubuntu 20.04 Focal Fossa”
Categories
Mandarin

Windows 10 Software Term Mandarin Chinese English – Part 1

I have changed all my Windows 10 language to Mandarin as part of practicing “普通话”. It’s very hard for me to find databases that show the software or desktop translation from Chinese to English. This is including common software like Words, Excel, Teams Chat, Outlook, and Calendar.

Here is part one in which you can learn :

New file新建Xīnjiàn
Open打开dǎ kāi
File文件wén jiàn
Download下载xià zǎi
Document文档wén dàng
Images图像tú xiàng
Photos照片zhào piàn
Continue reading “Windows 10 Software Term Mandarin Chinese English – Part 1”