<?php
function prepared_mysqli_query($query) {
//initializing required variables
$first_half = '';
$placeholders = '';
$data_types = '';
$values = '';
//getting the first half of query
$trim_end = strrpos($query,'(');
$first_half = substr($query, 0, $trim_end);
//getting the values to be inserted
$open = strrpos($query,'(');
$close = strrpos($query,')');
$values = substr($query,($open+1),($close+1));
$values_stripped = substr($values,0,-1);
$values_array = explode(",",$values_stripped);
$values_count = count($values_array);
//generating placeholders
$ph = "(";
for ($i = 1; $i < $values_count; $i++) {
$ph.= "?,";
}
$ph.= "?)";
$placeholders .= $ph;
//getting value types
$types ='';
for ($i = 0; $i < $values_count; $i++){
$str = gettype($values_array[0]);
$types .= $str[0];
}
$data_types .= $types;
//execution
$query = $first_half.$placeholders;
return ["query" => $query,
"data_types" => $data_types,
"values" => $values_array
];
}
var_dump(prepared_mysqli_query("INSERT INTO `tablename` (`column1`, `column2`, `column3`) VALUES (NULL, 'v1', 'v2');"));
var_dump(prepared_mysqli_query("INSERT INTO `tablename` (`column1`, `column2`, `column3`) VALUES (NOW(), 'v1', 'v2');"));
var_dump(prepared_mysqli_query("INSERT INTO `tablename` (`column1`, `column2`, `column3`) VALUES ('test,with,commas', 'v1', 'v2');"));
var_dump(prepared_mysqli_query("INSERT INTO `tablename` (`column1`, `column2`, `column3`) VALUES ('v0', 'v1', 'v2') ON DUPLICATE KEY UPDATE `column2` = 'v2';"));
- Output for 7.3.0 - 7.3.33, 7.4.0 - 7.4.33, 8.0.0 - 8.0.30, 8.1.0 - 8.1.28, 8.2.0 - 8.2.19, 8.3.0 - 8.3.4, 8.3.6 - 8.3.7
- array(3) {
["query"]=>
string(72) "INSERT INTO `tablename` (`column1`, `column2`, `column3`) VALUES (?,?,?)"
["data_types"]=>
string(3) "sss"
["values"]=>
array(3) {
[0]=>
string(4) "NULL"
[1]=>
string(5) " 'v1'"
[2]=>
string(6) " 'v2')"
}
}
array(3) {
["query"]=>
string(76) "INSERT INTO `tablename` (`column1`, `column2`, `column3`) VALUES (NOW(?,?,?)"
["data_types"]=>
string(3) "sss"
["values"]=>
array(3) {
[0]=>
string(1) ")"
[1]=>
string(5) " 'v1'"
[2]=>
string(6) " 'v2')"
}
}
array(3) {
["query"]=>
string(76) "INSERT INTO `tablename` (`column1`, `column2`, `column3`) VALUES (?,?,?,?,?)"
["data_types"]=>
string(5) "sssss"
["values"]=>
array(5) {
[0]=>
string(5) "'test"
[1]=>
string(4) "with"
[2]=>
string(7) "commas'"
[3]=>
string(5) " 'v1'"
[4]=>
string(6) " 'v2')"
}
}
array(3) {
["query"]=>
string(72) "INSERT INTO `tablename` (`column1`, `column2`, `column3`) VALUES (?,?,?)"
["data_types"]=>
string(3) "sss"
["values"]=>
array(3) {
[0]=>
string(4) "'v0'"
[1]=>
string(5) " 'v1'"
[2]=>
string(47) " 'v2') ON DUPLICATE KEY UPDATE `column2` = 'v2'"
}
}
- Output for 8.3.5
- Warning: PHP Startup: Unable to load dynamic library 'sodium.so' (tried: /usr/lib/php/8.3.5/modules/sodium.so (libsodium.so.23: cannot open shared object file: No such file or directory), /usr/lib/php/8.3.5/modules/sodium.so.so (/usr/lib/php/8.3.5/modules/sodium.so.so: cannot open shared object file: No such file or directory)) in Unknown on line 0
array(3) {
["query"]=>
string(72) "INSERT INTO `tablename` (`column1`, `column2`, `column3`) VALUES (?,?,?)"
["data_types"]=>
string(3) "sss"
["values"]=>
array(3) {
[0]=>
string(4) "NULL"
[1]=>
string(5) " 'v1'"
[2]=>
string(6) " 'v2')"
}
}
array(3) {
["query"]=>
string(76) "INSERT INTO `tablename` (`column1`, `column2`, `column3`) VALUES (NOW(?,?,?)"
["data_types"]=>
string(3) "sss"
["values"]=>
array(3) {
[0]=>
string(1) ")"
[1]=>
string(5) " 'v1'"
[2]=>
string(6) " 'v2')"
}
}
array(3) {
["query"]=>
string(76) "INSERT INTO `tablename` (`column1`, `column2`, `column3`) VALUES (?,?,?,?,?)"
["data_types"]=>
string(5) "sssss"
["values"]=>
array(5) {
[0]=>
string(5) "'test"
[1]=>
string(4) "with"
[2]=>
string(7) "commas'"
[3]=>
string(5) " 'v1'"
[4]=>
string(6) " 'v2')"
}
}
array(3) {
["query"]=>
string(72) "INSERT INTO `tablename` (`column1`, `column2`, `column3`) VALUES (?,?,?)"
["data_types"]=>
string(3) "sss"
["values"]=>
array(3) {
[0]=>
string(4) "'v0'"
[1]=>
string(5) " 'v1'"
[2]=>
string(47) " 'v2') ON DUPLICATE KEY UPDATE `column2` = 'v2'"
}
}
preferences:
106.02 ms | 405 KiB | 151 Q