Tips Query MySQL

PHP – MySQL

Anti SQL Injector

|-Cara

menggunakan prepared statement

|-Contoh

$input = 2022;

header('content-type: application/json');
include_once "database.php";

$conn = new mysqli($server, $user, $password, $db);
$conn->init();
$conn->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
$conn->ssl_set('/etc/mysql/ssl/client-key.pem', '/etc/mysql/ssl/client-cert.pem', '/etc/mysql/ssl/ca-cert.pem', NULL, NULL);
$conn->real_connect($server, $user, $password, $db, 3306, NULL, MYSQLI_CLIENT_SSL);
if ($conn->connect_error)
{
die('CONNERR');
}

$stmt = $conn->prepare("select nama, sum(hadir=1) as `hadir`, sum(hadir=0) as `tidak hadir` from presensi p join presensi_nama pn on pn.idpresensinama=p.idpresensinama where year(tanggal)=? group by p.idpresensinama");

$stmt->bind_param("i", $input);
$stmt->execute();

$result = $stmt->get_result();
if ($result->num_rows === 0)
{
echo json_encode('FAILED');
}
else
{
echo json_encode($result->fetch_all(MYSQLI_ASSOC));
}
$stmt->close();

|-Contoh dengan Klausa IN

$input = "2022-01-02, 2022-01-03";

header('content-type: application/json');
include_once "database.php";
$q      = explode(",", str_replace(" ", "", $input));
$params = [];
foreach ($q as $i => $val)
{
   $varname  = "var".$i;
   $$varname = $val;
   $params[] = &$$varname;
}

$conn = new mysqli($server, $user, $password, $db);
$conn->init();
$conn->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
$conn->ssl_set('/etc/mysql/ssl/client-key.pem', '/etc/mysql/ssl/client-cert.pem', '/etc/mysql/ssl/ca-cert.pem', NULL, NULL);
$conn->real_connect($server, $user, $password, $db, 3306, NULL, MYSQLI_CLIENT_SSL);
if ($conn->connect_error)
{
die('CONNERR');
}

$stmt = $conn->prepare("select nama, sum(hadir=1) as `hadir`, sum(hadir=0 or isnull(hadir)) as `tidak hadir` from presensi p join presensi_nama pn on pn.idpresensinama=p.idpresensinama where tanggal in (".trim(str_repeat("?,",count($params)),",").") group by p.idpresensinama"); 

call_user_func_array(array($stmt, "bind_param"), array_merge(array(str_repeat("s",count($params))), $params ));

$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows === 0)
{
echo json_encode('FAILED');
}
else
{
echo json_encode($result->fetch_all(MYSQLI_ASSOC));
}
$stmt->close();

Demikian, semoga bermanfaat. [bst]