pesan error : SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘.2fa, u.admin_template as template FROM user AS u WHERE u.username=’a…’ at line 3

Error ini terjadi karena ada titik (.) sebelum 2fa di query SQL Anda.

Perhatikan pesan error: near '.2fa, u.admin_template...'

Maksud error mungkin menulis u.2fa (tabel user dengan kolom 2fa), tetapi MySQL/MariaDB tidak mengizinkan nama kolom dimulai dengan angka (2fa) tanpa diapit backtick.

Penyebab:

Kolom bernama 2fa dianggap sebagai angka 2 diikuti teks setelah titik, sehingga u.2fa dibaca sebagai u.2 (angka desimal 2) lalu fa (alias atau kolom lain).

Di file Native.php baris 101 ada query yang bermasalah:

SELECT
    u.user_id as uid, u.username as uname, u.passwd,
    u.realname as realname, u.groups, u.user_image as upict, u.2fa, u.admin_template as template
    FROM user AS u
    WHERE u.username=?

u.2fa tanpa backtick menyebabkan error.

Perbaiki file Native.php:

nano /home/smknjatengpati/perpus.smknjatengpati.sch.id/lib/Auth/Methods/Native.php

Cari baris sekitar 101 (dalam fungsi adminAuthenticate).

Ubah dari:

$user = DB::query(<<<SQL
SELECT
    u.user_id as uid, u.username as uname, u.passwd,
    u.realname as realname, u.groups, u.user_image as upict, u.2fa, u.admin_template as template
    FROM user AS u
    WHERE u.username=?
SQL, [$this->username]);

Menjadi:

$user = DB::query(<<<SQL
SELECT
    u.user_id as uid, u.username as uname, u.passwd,
    u.realname as realname, u.groups, u.user_image as upict, u.`2fa`, u.admin_template as template
    FROM user AS u
    WHERE u.username=?
SQL, [$this->username]);

Atau cara alternatif (lebih aman):

$user = DB::query(<<<SQL
SELECT
    u.user_id as uid, u.username as uname, u.passwd,
    u.realname as realname, u.groups, u.user_image as upict, u.`2fa`, u.`admin_template` as template
    FROM `user` AS u
    WHERE u.username=?
SQL, [$this->username]);

Pastikan kedua file sudah diperbaiki:

Edit file /lib/Auth/Validator.php

Perbaikan 1: Baris 172 (fungsi findUserByToken)

DARI:

$sql = 'SELECT u.user_id AS uid, u.username AS uname, u.passwd, 
        u.realname AS realname, u.groups, u.user_image AS upict, u.2fa 
        FROM user AS u
        INNER JOIN user_tokens AS ut ON ut.user_id = u.user_id
        WHERE ut.selector = :selector AND ut.expires_at > now()
        LIMIT 1';

MENJADI:

$sql = 'SELECT u.user_id AS uid, u.username AS uname, u.passwd, 
        u.realname AS realname, u.groups, u.user_image AS upict, u.`2fa` 
        FROM user AS u
        INNER JOIN user_tokens AS ut ON ut.user_id = u.user_id
        WHERE ut.selector = :selector AND ut.expires_at > now()
        LIMIT 1';

Perbaikan 2: Baris 185 (fungsi findUserByUsername)

DARI:

$sql = 'SELECT u.user_id AS uid, u.username AS uname, u.passwd, 
        u.realname AS realname, u.groups, u.user_image AS upict, u.2fa 
        FROM user AS u WHERE u.username = :username';

MENJADI:

$sql = 'SELECT u.user_id AS uid, u.username AS uname, u.passwd, 
        u.realname AS realname, u.groups, u.user_image AS upict, u.`2fa` 
        FROM user AS u WHERE u.username = :username';