How to convert JSON to SQLI query - php
I am new to coding and I realized that I need to convert JSON to SQLi Query, but I cannot accomplish what I need.
I am trying to find a way to get a parameter from url and based on them use a generic function for INSER, UPDATE, DELETE or SELECT from my database.
JSON INSERT:
{"Username":"MyEmail@email.com","Password":"329670c3265b6ccd392e622733e9772f"}
JSON UPDATE:
{"Username":"MyEmail@email.com","Password":"newpacd392worda12123sdw112asddww"}
The above JSON is retrieved from a function that validates the URL query string and converts it to JSON
function php_raw_query() {
$raw = filter_input(INPUT_SERVER, 'QUERY_STRING', FILTER_SANITIZE_STRING);
$arr = array();
$pairs = explode('&', $raw);
foreach ($pairs as $i) {
if (!empty($i)) {
list($name, $value) = explode('=', $i, 2);
if(isset($arr[$name])){
if (is_array($arr[$name])){
$arr[$name][] = $value;
}
else {
$arr[$name] = array($arr[$name], $value);
}
}
else {
$arr[$name] = $value;
}
}
}
return $arr;
}
$json = json_encode(php_raw_query());
After that I need to create a request for a variable depending on the data passed, for example if I have a USER and a PASS, I can log in, register, request a change or delete a pass.
With this in mind, the user can do:
SELECT * FROM 'users' WHERE 'Username'='MyEmail@email.com' AND 'Password'='329670c3265b6ccd392e622733e9772f';
Note that both the column name (username) and the value ( MyEmail@email.com ) are from the JSON key, the value accordingly and the AND operator is only included if the JSON array has more than 1 element.
Can this be done?
source to share
This should be helpful to you
$object = json_decode('{"Username":"MyEmail@email.com","Password":"329670c3265b6ccd392e622733e9772f"}');
function generate($object) {
$query = 'SELECT * FROM table WHERE ';
$paramsArray = [];
foreach ($object as $key => $value) {
$paramsArray[] = "'$key' = '$value'";
}
return $query . implode(' AND ', $paramsArray) . ";";
}
echo generate($object);
But preparing queries this way is not safe. Have a look at https://pl.wikipedia.org/wiki/SQL_injection
source to share
@Lukasz I just used a piece of code for you and worked great. I applied a minimal change to the raw_query function, but it seems to work fine.
Also, I didn't understand things with SQL injection. Could you please understand in more detail?
Thank.
Url: _module.php?Username=MyEmail@email.com&Password=329670c3265b6ccd392e622733e9772f
`
ob_start();
function php_raw_query() {
$raw = filter_input(INPUT_SERVER, 'QUERY_STRING', FILTER_SANITIZE_STRING);
$arr = array();
$pairs = explode('&', $raw);
foreach ($pairs as $i) {
if (!empty($i)) {
list($name, $value) = explode('=', $i, 2);
if (isset($arr[$name])) {
if (is_array($arr[$name])) {
$arr[$name][] = $value;
} else {
$arr[$name] = array($arr[$name], $value);
}
} else {
$arr[$name] = $value;
}
}
}
return json_encode($arr);
}
$object = json_decode(php_raw_query());
function generate($object) {
$query = 'SELECT * FROM table WHERE ';
$paramsArray = [];
foreach ($object as $key => $value) {
$paramsArray[] = "'$key' = '$value'";
}
return $query . implode(' AND ', $paramsArray) . ";";
}
echo generate($object);
ob_end_flush();
`
AJAX:
`
$(document).on('submit', 'form', function (e) {
var information = $(this).serialize();
e.preventDefault();
$.ajax({
url: "_module.php",
data: information,
type: "GET",
dataType: "json",
success: function (data) {
$("#post-wrapper").html(data);
},
error: function () {
console.log('Cannot retrieve data.');
}
});
}); `
The output div shows: SELECT * FROM table WHERE 'username' = 'MyEmail%40email.com' AND 'password' = '329670c3265b6ccd392e622733e9772f';
source to share