Column selection

Column selection can be used to select a rectangular area of a file.

Mac

Using mouse

Left Mouse Button + ⌥

OR: Middle Mouse Button

Add to selection: ⌘

Subtract from selection: ⇧+⌘

Using keyboard

Ctrl+Shift+⬆

Ctrl+Shift+⬇

For the general programming knowledge.

Off-By-One [溢出]

• 严格来说，off-by-one是一种特殊的溢出漏洞，off-by-one指程序向缓冲区中写入时，写入的字节数超过了这个缓冲区本身所申请的字节数并且只越界了一个字节。
• off-by-one，是指单字节缓冲区溢出，这种漏洞的产生往往与边界验证不严和字符串操作有关，也不排除写入的size正好就只多了一个字节的情况。
• 一般认为，单字节溢出是难以利用的，但是因为Linux的堆管理机制ptmalloc验证的松散性，基于Linux堆的off-by-one漏洞利用起来并不复杂，并且威力强大。
• off-by-one是可以基于各种缓冲区的，比如栈、bss段等等，但是堆上（heap based）的off-by-one是CTF中比较常见的。

I try to list out the questions during working on Postgresql here for the future reference.

How to convert date string to timestamp in postgresql?

Use function TO_TIMESTAMP

1
2
3

SELECT TO_TIMESTAMP('2017-03-31 9:30:20','YYYY-MM-DD HH:MI:SS');
-- it will return as following:
2017-03-31 09:30:20.000000

How to return a string value from jsonb in Postgresql?

Use jsonb->>key operator, be carefuly if you use jsonb->key it will return back jsonb type, not a string.

1
2
3
4
5
6
7

select '{
  "first_name": "Adam",
  "last_name": "Smith",
  "created_at": "2021-12-07 03:08:57",
  "updated_at": "2021-12-07 03:09:00"
}'::jsonb->>'first_name';
-- it will return `Adam`

Can I extract the info from jsonb in postgresql?

jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb

Extracts JSON sub-object at the specified path. (This is functionally equivalent to the #> operator, but writing the path out as a variadic list can be more convenient in some cases.)

1

json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6') → "foo"

How to put log in stored procedures?

You need to change the config file postgresql.conf and set log_min_messages = notice, there are some options you can choose, check the config file.

IS NOT NULL work with composite type data

If you have a value something that is a composite type, for example a row of a table, something IS NOT NULL is only true if none of the attributes of the composite are NULL. check more details here

How to save 24hours time format

if you see some error like this Postgresql ERROR: hour "16" is invalid for the 12-hour clock you could do this TO_TIMESTAMP(i_test_date ->> 'created_at', 'YYYY-MM-DD HH24:MI:SS') to save the time as 24hours format

Git commands

list some commands often being used:

Delete branch

• Deleting local branches

1

git branch -d feature/add-new-feature

• Deleting remote branches in Git

1

 git push origin --delete feature/add-new-feature

Change branch name

• change name of local branch

1
2
3
4
5
6

git checkout <branch>
git branch -m <new_branch_name>

##example
git checkout feature
git branch -m quickfix

• change name of remote branch
  • you have to change branch name locally first
  • then do the following:

1
2
3
4
5
6
7
8
9

git push <remote> :<old_branch_name> <new_branch_name>

#you have to set the upstream branch for the newly created branch using the “git upstream” command.
git push <remote> -u <new_branch_name>

git push origin :feature quickfix
git push origin -u quickfix
## example

Is it possible to convert SQLite to MySQL “as is”?

Probably the quick and easiest way to convert SQLite to MySQL includes two general steps:

• Export SQLite database to dump file using SQLite .dump command.

1

sqlite3 sample.db .dump > dump.sql

• You can then (in theory) import SQLite dump into the MySQL database.

1

mysql -p -u root -h 127.0.0.1 test < dump.sql

“In theory” means that solution wouldn’t work as there are a few differences in grammars between MySQL and SQLite.

There are just some differences between SQLite and MySQL syntax