How to put CURL returned json in to mysql blob?

[fumeeptc]

Hello, long time no question so here is one:

How to put API/CURL returned json in to mysql blob?
I need it to be uploaded in blob. I tried looking around and have not find anything!

Thanks in advance!

[m_hutley]

blob isnt really the right column type for json (Which is a regular string), as it holds binary strings. That said, MySQL will happily chomp up a regular string into a blob field.

It’s the same as pushing any other string into a VARCHAR or TEXT field; where exactly are you struggling? What’s your code look like at the moment (dont post any database passwords!)?

[fumeeptc]

Well I have dynamic form that can upload .json files to same blub field:

$fileName = $_FILES['file']['name'];
$tmpName  = $_FILES['file']['tmp_name'];
$fileSize = $_FILES['file']['size'];
$fileType = $_FILES['file']['type'];

$fp      = fopen($tmpName, 'r');
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
fclose($fp);

if(!get_magic_quotes_gpc())	{
	$fileName = addslashes($fileName);
}

$Query = $main->con->prepare("
	INSERT INTO fileLines (name, size, type, content) VALUES (?, ?, ?, ?)
");
$Query->execute([$fileName, $fileSize, $fileType, $content]);

But now I have response from curl where i get json data. And what I need is to upload that to blob similar as it is done in code above!?

I tried this but it does not work. … Json is broken. …

$premadeXLM = base64_encode($result);

if($premadeXLM){
	$name='randomName.json';
	$type='application/json';
	$size=strlen(json_encode($premadeXLM, JSON_NUMERIC_CHECK));
	$content=$premadeXLM;
}

[m_hutley]

@fumeeptc

$premadeXLM = base64_encode($result);

So you turn the JSON into a base64 string.

@fumeeptc

$size=strlen(json_encode($premadeXLM, JSON_NUMERIC_CHECK));

You then try to json_encode the base64 string, but that’s not going to work, because it’s not a JSON anymore, its a base64 encoded string. JSON_encode is designed to take an object and put it into a json string.

[m_hutley]

So you’re going to get a lot of people come in here and say to use PDO. To use parameterized queries (you already know thats coming because of your PS), etc.

How do you know your “json is broken”?

[fumeeptc]

@fumeeptc

$size=strlen(json_encode($premadeXLM, JSON_NUMERIC_CHECK));

My mistake:
$size=strlen(json_encode($result, JSON_NUMERIC_CHECK));
But it still does not work. …

[fumeeptc]

When I try to pass it to next operation it does not do what was planned. …

File uploaded from POST that contains the same json data works but this example does not gets read.

[m_hutley]

So what string gets written to your database?

[fumeeptc]

@m_hutley

So you’re going to get a lot of people come in here and say to use PDO. To use parameterized queries (you already know thats coming because of your PS), etc.

Edited!

[fumeeptc]

At the end it looks like any other blob. … Just it is not 100% to file that uploads via POST

[m_hutley]

So if I take the following json

{
"Test":"Begins here",
"Stuff": ["Things","More"]
}

and you write it to your database, you should see it as
ewoiVGVzdCI6IkJlZ2lucyBoZXJlIiwKIlN0dWZmIjogWyJUaGluZ3MiLCJNb3JlIl0KfQ==

What do you see instead?

[fumeeptc]

@m_hutley

{
"Test":"Begins here",
"Stuff": ["Things","More"]
}

You know that blob coverts it right?
If I add plain json data in that field I get:
0x43006F006E006E00650063007400650064003A0020003000300000

[m_hutley]

@fumeeptc

You know that blob coverts it right?

Which is why blob is the wrong column type to store nonbinary data. But you insisted

@fumeeptc

If I add plain json data in that field I get:
0x43006F006E006E00650063007400650064003A0020003000300000

and if you convert that hex back to a string… do you get anything remotely resembling json?

[fumeeptc]

It does not seems the same. … I will ask this. … How to get the same result as in working example in POST upload?

[m_hutley]

So have you made sure that what you’re inputting is a json to begin with?

echo "Before 64: ".$result;
$premadeXLM = base64_encode($result);
echo "After 64: ".$premadeXLM;