PHP json_encode an array & store return in mysql

Question

I want to store a php array as JSON in mysql. for that I have meeting_point_json column with type='longtext'.

here is the array:

Array
(
    [1] => Array
        (
            [date] => 23/4/2012
            [meeting_time] => 23:04
            [meeting_place] => town hall
            [venue] => London
            [opponents] => Tigers
            [official_incharge] => Mr Putin
        )

    [2] => Array
        (
            [date] => 23/4/2050
            [meeting_time] => 13:04
            [meeting_place] => chief office
            [venue] => Kenya
            [opponents] => Peococks
            [official_incharge] => Mr Black
        )

    [3] => Array
        (
            [date] => dsad
            [meeting_time] => sadas
            [meeting_place] => jjjjj
            [venue] => jjjj
            [opponents] => dasds
            [official_incharge] => asad
        )

)

and here is the php code:

$data = json_encode($_POST['team_meeting_pt']);
    $sql = "UPDATE yami_sub_team set meeting_point_json = $data where id = $subteam_id";
    if(mysql_query($sql)){
        exit("Done!");
    }else{
        die('Something went wrong, changes not saved. Error details: ' . mysql_error());
    }

it should work but Instead, I get an error:

Something went wrong, changes not saved. Error details: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"1":{"date":"23\/4\/2012","meeting_time":"23:04","meeting_place":"town hall","ve' at line 1

Any idea what I am doing wrong here?

what If _POST [''team_meeting_pt] is empty or worse NOT SET ?
@BerryLangerak atleast json_encode will mess up the attack .. :)
@Svetlio Yeah, but still... there's a security hole now. In fact, the reason he gets this error is because he is injecting non-SQL into his SQL statement ;)

Michael Antonius · Accepted Answer · 2012-11-01 13:41:39Z

See at: $data variable change with ".$data." and $subteam_id variable with ".$subteam_id."

remember the sql query only sent as strings, not variable inside there... and do it to your all way to write PHP. Always wrap variable with ".." and '..'

".." and '..' are depending by your strip wrapper: For example: If look like this $string = mysql_query("SELECT DATA FROM ".$variable.""); So, you must use ".." wrapper ! and if like this $string = mysql_query('SELECT DATA FROM '.$variable.''); So, you must use '..' wrapper !

Good luck friend

Bang Dao · Answer 2 · 2012-11-01 12:27:54Z

It should be like this:

$sql = "UPDATE yami_sub_team set meeting_point_json = '" . mysql_escape_string($data) . "'where id = $subteam_id";

You can read more about that function here: http://php.net/manual/en/function.mysql-escape-string.php

anonym · Answer 3 · 2012-11-01 12:34:42Z

up vote 0 down vote

Try this line instead of your:

$sql = "UPDATE yami_sub_team SET `meeting_point_json`='".$data."' WHERE `id`='".$subteam_id."';";

answered Nov 1 '12 at 12:34

anonym
1

1

Still prone to SQL injection. Use mysql_real_escape_string, or rather, don't use the deprecated mysql_* functions and choose PDO with a prepared statement. – Berry Langerak Nov 1 '12 at 12:37

Svetlio · Answer 4 · 2012-11-01 12:36:58Z

up vote 0 down vote

$data = "'" . mysql_escape_string(json_encode($_POST['team_meeting_pt'])) . "'";

Change at 1st line

answered Nov 1 '12 at 12:36

Svetlio
2,4711522

asked	10 months ago
viewed	319 times
active	10 months ago

PHP json_encode an array & store return in mysql

4 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql json or ask your own question.

PHP json_encode an array & store return in mysql

4 Answers

Your Answer

Sign up or login

Post as a guest

Not the answer you're looking for? Browse other questions tagged php mysql json or ask your own question.

Related