Kendala Mysql utf8mb4_general_ci

 -- set your table name here

SET @MY_SCHEMA = "";

-- tables
SELECT DISTINCT
    CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;") as queries
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=@MY_SCHEMA
  AND TABLE_TYPE="BASE TABLE"

UNION

-- table columns
SELECT DISTINCT
    CONCAT("ALTER TABLE ", C.TABLE_NAME, " CHANGE ", C.COLUMN_NAME, " ", C.COLUMN_NAME, " ", C.COLUMN_TYPE, " CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;") as queries
FROM INFORMATION_SCHEMA.COLUMNS as C
    LEFT JOIN INFORMATION_SCHEMA.TABLES as T
        ON C.TABLE_NAME = T.TABLE_NAME
WHERE C.COLLATION_NAME is not null
    AND C.TABLE_SCHEMA=@MY_SCHEMA
    AND T.TABLE_TYPE="BASE TABLE"

UNION

-- views
SELECT DISTINCT
    CONCAT("CREATE OR REPLACE VIEW ", V.TABLE_NAME, " AS ", V.VIEW_DEFINITION, ";") as queries
FROM INFORMATION_SCHEMA.VIEWS as V
    LEFT JOIN INFORMATION_SCHEMA.TABLES as T
        ON V.TABLE_NAME = T.TABLE_NAME
WHERE V.TABLE_SCHEMA=@MY_SCHEMA
    AND T.TABLE_TYPE="VIEW";

Query SQL Server Create User And Grant Select to single database

 CREATE LOGIN [username] WITH PASSWORD = 'password', DEFAULT_DATABASE = [database_name]

GO

USE database_name;

CREATE USER username FOR LOGIN username;

GO 

GRANT SELECT TO username

GO

Setup Gitlab CI / CD git pull tanpa password

 1. ubah atau clone dari gitlab dengan ssh url

git remote set-url origin {url_ssh}

2. buat ssh key di server cpanel jika belum ada :

ssh-keygen -t ed25519 -C "youremailaddress@domain.tld DAY-MONTH-YEAR" -f ~/.ssh/my_key

3. bikin file config di ~/.ssh

diisi :

Host remote-git-repo-domain.tld
    IdentityFile ~/.ssh/my_key

setup ssh to server

copas ssh self to server

 cat ~/.ssh/id_rsa.pub | ssh root@ip_address "cat >> ~/.ssh/authorized_keys"

Mengamankan CodeIgniter

 Contoh Controller :

<?php

class Jobvacancy extends CI_Controller

{

public function addjob()

{

$this->load->library('form_validation');

if ($this->input->method() === "post") {

$data["job"] = $this->input->post();

$rules = [

[

'field' => 'name',

'label' => 'Name',

'rules' => ''

],

[

'field' => 'job_image_url',

'label' => 'Image URL',

'rules' => 'valid_url'

],

[

'field' => 'job_url',

'label' => 'Job URL',

'rules' => 'valid_url'

],

];

$this->form_validation->set_rules($rules);

if ($this->form_validation->run() == FALSE) {

return $this->load->view('add_job_form');

}

// di sini biasanya dilakukan penyimpanan ke database

return $this->load->view("list_job", $data);

}

$this->load->view('add_job_form');

}

}

Contoh View

<form id="form-add" action="" method="post">

<label for="name">Vacancy Job name</label>

<input type="text" name="name" value="<?= htmlentities(set_value('name'), ENT_QUOTES) ?>">

<div style="color: tomato;">

<?= form_error('name') ?>

</div>

<br>

<label for="job_image_url">Image URL</label>

<input onchange="previewImage(this)" type="url"

pattern="[(http(s)?):\/\/(www\.)?a-zA-Z0-9@:%._\+~#=]{2,256}\.[a-z]{2,6}\b([-a-zA-Z0-9@:%_\+.~#?&//=]*)"

name="job_image_url" value="<?= htmlentities(set_value('job_image_url'), ENT_QUOTES) ?>">

<div style="color: tomato;">

<?= form_error('job_image_url') ?>

</div>

<div id="image-preview"></div>

<br>

<label for="job_url">Job URL</label>

<input type="url"

pattern="[(http(s)?):\/\/(www\.)?a-zA-Z0-9@:%._\+~#=]{2,256}\.[a-z]{2,6}\b([-a-zA-Z0-9@:%_\+.~#?&//=]*)"

name="job_url" value="<?= htmlentities(set_value('job_url'), ENT_QUOTES) ?>">

<div style="color: tomato;">

<?= form_error('job_url') ?>

</div>

<br>

<input type="submit" value="Simpan">

</form>

<script type="text/javascript">

function previewImage(event) {

const previewContainer = document.querySelector('#image-preview');

const form = document.querySelector('#form-add');

if (form.checkValidity()) {

const imageURL = event.value;

previewContainer.innerHTML = `

<img src="${imageURL}" height="200" width="200" />

`;

}

}

</script>

DUMP & RESTORE POSTGRE

Backup :

 sudo pg_dump -U {user} -h {host} {db_name} > /{path}/{file}.sql

Restore :

psql -U {user} -h {host} {db_name} < /{path}/{file}.sql

Update Letsencrypt

 certbot -d domain.com --force-renewal