ilbiffi

There was also a missing "while" in the second script while ($stmt->fetch()) { $json[] = array('a'=>$a, 'b'=>$b, 'c'=>$c, 'd'=>$d, 'e'=>$e, 'f'=>$f, 'g'=>$g, 'h'=>$h, 'i'=>$i); } $stmt->close(); } $link->close(); print json_encode($json); Thanks!

I'm trying to retrieve data and I want it formatted as json, I'm trying with two scripts, they're not working fully for different reasons, the first formats the data as an array, with square brackets instead of curly ones. $stmt = $link->prepare("select * from x where y = ?"); $stmt->bind_param('s', $z); $stmt->execute(); $stmt->bind_result($a, $b, $c, $d, $e, $f, $g, $h ,$i); while ($stmt->fetch()) { $output[]=array($a, $b, $c, $d, $e, $f, $g, $h ,$i); } $stmt->close(); print json_encode($output); Which gives this output [[7,"xyz","abc","123","lorem ipsum",11,22,33,44],[8,"zyx","cba","321","muspi merol",44,33,22,11]] This other version outputs curly brackets but only displays the first row, not all of them $sql = 'select * from x where y = ?'; if ($stmt = $link->prepare($sql)) { $stmt->bind_param('s', $z); $stmt->execute(); $stmt->bind_result($a, $b, $c, $d, $e, $f, $g, $h ,$i); $json = array(); if($stmt->fetch()) { $json = array('a'=>$a, 'b'=>$b, 'c'=>$c, 'd'=>$d, 'e'=>$e, 'f'=>$f, 'g'=>$g, 'h'=>$h, 'i'=>$i); }else{ $json = array('error'=>'no record found'); } $stmt->close(); } $link->close(); print(json_encode($json)); ?> I would like to have curly brackets, but something fails in the second script and I can't retrieve all the values, what am I missing?

Super-clear, thanks. So the correct way is simply this prepared statement $st = mysqli_prepare($link, 'INSERT INTO `a`.`b` (x,y,z) VALUES (?, ?, ?)'); mysqli_stmt_bind_param($st, 'sss', $x, $y, $z); $data = json_decode($json, true); foreach ($data as $row) { $x = $row['x']; $y = $row['y']; $z = $row['z']; mysqli_stmt_execute($st); } and nothing else, right?

Yep, that's a copy and paste error.

I know that my data must have a certain format, so any kind of special character to me is not valid; I know my windows client application is well-behaved but if someone tries to execute my public page with my post variables and inserts malicious characters shouldn't I protect my web app? What do you mean by duplicate json_decode, my understanding was I needed it to "explode" a json string that contains several rows of data. My usage here is that once all the rows are extracted, every field must be cleaned before insertion; why a prepared statement alone should be considered enough to protect my db? Thanks

Hi, I want to sanitize json strings encoded by my application, my concern is that an attacker could forge a fake json string to launch malicious code. Can you please validate my method? This is what I do (I attach only the relevant portions of the script): $json = trim($_POST['json']); $data = json_decode($json, true); $st = mysqli_prepare($link, 'INSERT INTO `a`.`b` (x,y,z) VALUES (?, ?, ?)'); mysqli_stmt_bind_param($st, 'sss', $x, $y, $z); $data = json_decode($json, true); foreach ($data as $row) { $x1 = $row['x']; $x2 = trim($x1); $x = mysqli_real_escape_string($link, $x2); $y1 = $row['y']; $y2 = trim($y1); $y = mysqli_real_escape_string($link, $y2); $z1 = $row['z']; $z2 = trim($z1); $z = mysqli_real_escape_string($link, $z2); mysqli_stmt_execute($st); } So basically I trim the raw json string, then I extract all the data and just before executing the insert I sanitize the data. It works because my db gets updated, I was wondering if it works also from a security perspective. My understanding is that "mysqli_real_escape_string" is a sort of general purpose sanitize function right? Thanks

The issue was not the mysql query, but the php statements to correctly take the json encoded string and pass it to the mysql, I made it by using $data = json_decode($json, true); foreach ($data as $row) { $i = $row['i']; $x = $row['x']; $y = $row['y']; mysqli_stmt_execute($st); }

Yes, I have complete control over the format, I can send the text in json, xml or whatever. The data source is a client and has the ability to send POST request, the human intervention is just a button that triggers a call to a php script on the web server. Using this method I can easily retrieve output from select queries and store it my client's internal arrays. This application produces sort of geospatial data, so it will be long lists of coordinates and strings, as they relate to objects in a 3d space and tags of these objects. The data is therefore available as 3d objects, their properties can be easily put into the client's arrays. The problem is that I feel it would be better to avoid the creation of a separate file by the client, as I'd rather avoid write permissions, antivirus warnings, and so on. If the client could send the data directly it would be better, but my problem is in that case do you think that sending a few hundreds of lines all together is viable? do you suggest to batch the operation so that it's maximum 10 lines every time? or to send one line at a time?

JSON is more or less clear to me, is a sort of smarter way of formatting values; in fact I found a working script that reads from mysql and returns json-formatted data (I omit the $link and $query script as I suppose they're of no interest, they're the classic mysql connection and select). <form method="post"> <name="user" /> <name="pass" /> </form> <?php include("../mapdb.php"); $result = mysqli_query($link, $query) or die("Error in Selecting " . mysqli_error($link)); //create an array $emparray = array(); while($row =mysqli_fetch_assoc($result)) { $emparray[] = $row; } echo json_encode($emparray); //close the db connection mysqli_close($connection); ?> This gives me the following output [{"i":"1","x":"12.213","y":"43.32"},{"i":"2","x":"-23.23","y":"43.234543"},{"i":"3","x":"-0.00123","y":"-2134.2332"},{"i":"4","x":"0.5432","y":"-8.3216564"}] which is ok. Now the problem is doing the reverse, where do I put json data in a post? The examples I've found so far all say to create a json file and then refer to it in a php script via the "json_decode" command. However the problem is that I'd need to send data from a normal windows client with no php interpreter on it. In practice I'd have to put json data directly from a POST variable, to be read by a php script on my server, and then put into a mysql db. Is this viable? can you post an example on how to format the file? Marco

Hello, I'm new to the forum, and I need a suggestion. I have an application that generates large arrays, with potentially hundreds of entries. I would like to store them in a mysql db via php, and to retrieve them by the same means. To retrieve the data doesn't look particularly troublesome, as I think that any limit to downloadable data is really much higher than my needs. The real problem would be the best way to upload the data using POST. I have read that the serialize function is not really the best, but I have yet to understand the real troubles in using it. Somewhere else I've read the suggestion to upload a file to my webserver and then to have mysql read and insert the contents, but this seems a bit clunky and probably much less safe. What would you do in this case? Kind regards Marco