0

I am trying to use .getJSON to post one value to a url, and have it return all corresponding values for that one value.

All values are stored in a MySQL MyISAM table.

The request is:

$.getJSON('queries.php', { key: $("#cfkey").val()},  
function(result) {
$.each(result, function(i,r) {
    console.log("user:" + r.username + "Addr:" + r.addr);
    setItem("_cfuser", r.username);
    //console.log("user:" + r.username + "saved");
});
});

cfkey, is a unique key assiged to each "user", and the idea is to get this function to return only corresponding results to that key.

PHP Code: 

//error_reporting(E_ALL); ini_set("display_errors", 1);
include 'db/dbcon.php';
$input = $_GET['key'];
$input2 = $_GET['val'];
if($_GET) {
$q = "SELECT * FROM `cfaddrbook` WHERE '".$input."' = '".$input2."'";
} else {
$q = "SELECT * FROM `cfaddrbook`";
}
//Start connection with SQL
$res = $mysqli->query($q) or trigger_error($mysqli->error."[$q]");
$array = array(); // initialize
while($row = $res->fetch_array(MYSQLI_BOTH)) {
 $array[] = array(
'key' => $row[0],
'username' => $row[1],
'password' => $row[2],
'nick'     => $row[3],
'addr'     => $row[4],
'facebook' => $row[5],
'twitter'  => $row[6],
'linkedin' => $row[7],
'youtube'  => $row[8]
// ... continue like this
);
}
header('Content-Type: application/json');
echo json_encode($array);
$res->free();
$mysqli->close();

Currently, the function returns all usernames, and all addresses for one single key. I would like to send a request for a key, and get only corresponding data back, not other keys' data. I would also like to be able to input queries?key= and get back corresponding data that way as well.

EDIT: Working code:

include 'db/dbcon.php';
$thekey = $_GET['key'];
$input = $mysqli->real_escape_string($thekey);
if($_GET) {
$q = "SELECT * FROM `cfaddrbook` WHERE `key` = ".$input."";
} else {
$q = "SELECT * FROM `cfaddrbook`";
}
//Start connection with SQL
$res = $mysqli->query($q) or trigger_error($mysqli->error."[$q]");
$array = array(); // initialize
while($row = $res->fetch_array(MYSQLI_BOTH)) {
  $array[] = array(
'key' => $row[0],
'username' => $row[1],
'password' => $row[2],
'nick'     => $row[3],
'addr'     => $row[4],
'facebook' => $row[5],
'twitter'  => $row[6],
'linkedin' => $row[7],
'youtube'  => $row[8]
);
}
header('Content-Type: application/json');
echo json_encode($array);
$res->free();
$mysqli->close();
user3259138
  • 331
  • 2
  • 6
  • 19
  • 2
    JavaScript isn't magically adding usernames to your JSON, so it sounds like your PHP code is returning the wrong data. Post the PHP. – quietmint Feb 01 '14 at 04:48
  • Added the PHP code. I only want the ajax to return corresponding values to that key, not all the addresses and usernames for other keys. – user3259138 Feb 01 '14 at 19:49
  • Sorry, I didn't mean to phrase it a way that sounded like an order. My apologies. – user3259138 Feb 01 '14 at 20:13
  • Be sure to sanitize your database input though! Make sure SQL Injection or any other exploit isn't possible. http://bobby-tables.com/ – Deep Frozen Feb 01 '14 at 20:48
  • I tried mysqli_escape_string for the $_GET variables, but it made them blank, and it gave a mysqli error in the error logs stating that the values were blank. – user3259138 Feb 01 '14 at 20:52
  • Figured out the escape problem. Please reference: http://stackoverflow.com/questions/3005135/mysql-real-escape-string-just-makes-an-empty-string and the answer is by user @Panique – user3259138 Feb 01 '14 at 21:07

1 Answers1

-1

You have several problems.

  1. getJSON sends a GET request, but you're trying to read from $_POST.
  2. Even if it used POST, you're sending cfkey but reading key in your PHP code.
  3. You're not doing any sort of escaping on the value, so this would open you to easy SQL injection.

Because of 1 and 2 specifically, your code isn't actually branching how you think it is, so it's running your "select everything" path instead of the "select one record" path.

Debugging tips for the future: Use Chrome or Firefox and watch the network activity - you can see if things are GET or POST, then add var_dump throughout your PHP dumping bits of data to make sure the values you think are getting in there are actually getting there.

Collin Grady
  • 2,226
  • 1
  • 14
  • 14
  • If I use get JSON, and then change the PHP to if($GET) { and change the input variable to input = $_GET, then the get json call says the server returned with a 500 error? – user3259138 Feb 01 '14 at 20:12
  • Would it be better to use .postJSON or something? – user3259138 Feb 01 '14 at 20:13
  • If you get a 500 error, chances are you typod something; what is your actual error? Can you check the error log? There's no such thing as `postJSON` but you can use `post` itself to do this. – Collin Grady Feb 01 '14 at 20:14
  • PHP error: [01-Feb-2014 13:16:15 America/Denver] PHP Notice: 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 'key = '111111'' at line 1[SELECT * FROM `cfaddrbook` WHERE key = '111111'] in path/engine/queries.php on line 12 – user3259138 Feb 01 '14 at 20:17
  • [01-Feb-2014 13:16:15 America/Denver] PHP Fatal error: Call to a member function fetch_array() on a non-object in path/engine/queries.php on line 14 – user3259138 Feb 01 '14 at 20:18
  • Well there you go; you have an SQL error. Try escaping `key` with backquotes as well. – Collin Grady Feb 01 '14 at 20:23
  • Updated the PHP. The code above does not return 500 errors, does not return mysql errors. It simply says key and val are undefined. If I replace input with just WHERE key =, and leave input 2, then I get SQL syntax errors like the ones above. Escaping the strings doesn't seem to help? – user3259138 Feb 01 '14 at 20:38
  • Actually, Escaping the string seems to make the value blank. – user3259138 Feb 01 '14 at 20:44
  • Figured how how to make the query work. I will update the page with working code. But I cannot figure out how to sanitize it without making the value blank. Any suggestions? – user3259138 Feb 01 '14 at 21:01
  • Figured it out, please reference: http://stackoverflow.com/questions/3005135/mysql-real-escape-string-just-makes-an-empty-string and the answer is by user @Panique – user3259138 Feb 01 '14 at 21:06
  • I said to escape `key` not the value - literally the word `key` which is a reserved word in MySQL; i.e. `PRIMARY KEY` – Collin Grady Feb 01 '14 at 21:43