0

I am using prepared queries and binding variables to placeholders. This is done during inserting of data. This works well and I normally do not have problems.

However, on our registration form, someone put a value of St. John's into a form field. The data was successfully written and it seems alright from my phpMyAdmin console. However, any attempt to get data from that record do not work. The execution doesn't seem to fail as I have a try/catch around the execute and the catch would email me. If I remove the apostrophe then I can retrieve the record as normal.

I've searched around and all I see are people saying to use prepared statements. I am using them. They also say that addslashes() is a bad thing (and I don't use that). I've also tried using ':hospital' => $db_pdo->quote($_POST['hospital']) but the results are that the inserted fields have extra apostrophes which I do not want. Perhaps there is some sort of unquote method?

Also, it may be useful to know that I am fetching the entire row as an assoc_array and then json_encoding it so that I can send it back to be interpreted as JavaScript where each part of the array would be written to the appropriate field on a form.

So, I've decided to ask a question myself.

Example code: Insertion: ':fname' => $_POST['fname'], ENT_QUOTES ,

Retrieval: $db_pdo->prepare($statement_SQL); 

$db_pdo->bind(':preregkey', $_POST['id']);

$retval_execute = $db_pdo->execute();

if($retval_execute){$retval_execute='true';}
else{$retval_execute = 'false';}
<br>";

$result = $db_pdo->statement->fetch(PDO::FETCH_ASSOC);
$output = json_encode($result);
echo "<script type='text/javascript'>"; 
echo " var formdata = '" . $output . "';";
echo "console.log('value: ', " . $_POST['id'] . ")" ;
echo "</script>";

I use $.post to get the data and I think I'm having a problem with characters actually returned. I get illegal character or unexpected identifier. It seems that everything in 'data' as a result is dumped and lost.

// Gather the information for one pre-reg record, put the data into the form, switch to     the basic tab. 
function displayrecord_prereg(id) {
    // We were passed the id for the record, go get it!

    $.post('admin-board_p.php', { o: 'displayrecord_prereg', id: id}, function(data) {
        // Results have to go somewhere...
        console.log('data: ', data);
        $('#utilitydiv').html(data);
        // Make the JSON_ENCODE array actually usable in Javascript. 
        var formdata2 = JSON.parse(formdata);

        // Distribute the array of fields into their correct form field.
        // Must do it within this part of the function.
        $("#studentphoto").prop("value", formdata2['studentphoto']); 
        $("#grade").val(formdata2['grade']);
Nicksen782
  • 23
  • 1
  • 6
  • 1
    Cane we (a) see your code, and (b) have a clearer description of "do not work", please. – Matt Gibson Jun 30 '14 at 21:15
  • Can you double check the values of `$_POST['id']` and `$output`? What do you get? – Matthew Johnson Jun 30 '14 at 21:40
  • 1
    What does `var_dump($result)` output? – Mike Jun 30 '14 at 21:48
  • var_dump won't output due to how I am bringing in the data with Ajax/$.post. However, within the function I can email myself the result. The data appears valid like an array. I do see the "'" in some fields. It seems that character isn't liked when I do $('#utilitydiv').html(data); ... Then my next console error is formdata not defined which leads me to believe that it gets dumped entirely. – Nicksen782 Jul 01 '14 at 17:27

1 Answers1

0

Matthew Johnson came closest to an answer as he reminded me of the very important step of sanitizing the data from the DB. Trust no data source, especially if the data was entered by a validated user or just a user period.

I have solved this problem myself after much Google searching and fact-checking.

I don't know exactly why I can pass a JSON_ENCODE array from PHP and output that to a div id and it works, yet it won't work if there are single quotes in the data. the JSON_ENCODE array would work fine in JavaScript except if some of the data had a single quote in it.

I was using $.post to request data. It is simple and works but I wasn't specifying the datatype. $.post is just a shortcut for simplified POST Ajax so I decided to read up on $.ajax. This allowed me to specify the return datatype. Also, I found that it was unnecessary to put the output of the PHP into a div. The output goes to a variable called 'data' anyway and it is optional to put that 'data' into a div. If the ONLY THING that you output from the PHP script is a JSON_ENCODE array then JavaScript can use the 'data' result directly. The ACTUAL reason for the errors I was getting was because I was trying to enter data with invalid characters into that div.

I went all over the internet for this so hopefully someone finds this question and reads the following code:

JavaScript:

function displayrecordvalues(id) {
$.ajax({
    url: 'handler.php', 
    data: { o: 'displayrecord', id: id } ,
    type: "POST",
    dataType: 'json'
})
.success(
    function(formdata2){
        $("#studentphoto").prop("value", formdata2['studentphoto']); 
        $("#grade").val(formdata2['grade']); 
        $("#birthday").prop("value", formdata2['birthday']); 
        $("#gender").prop("checked", true);
        $("#fname").prop("value", formdata2['fname']); 
        $("#mname").prop("value", formdata2['mname']); 
        $("#lname").prop("value", formdata2['lname']); 
... etc...
    }
)
.fail(
    function(data){
        alert('error');
        console.log( "Data:", data);
    }
); 
}

PHP: ... Get your SQL data row into a variable called $result, remove any undesirable characters, then echo ONLY one line which would be the last line in this example.

// Intended for 1-row recordset
// Sanitize the data! (remember the '&' !)
$badchars = array("--", ";", );
foreach($result as &$v){
    $v = strip_tags($v);
    $v = htmlspecialchars($v); 
    $v = str_replace($badchars, "", $v);
}

echo json_encode($result);

DONE! Magic.

Brad Larson
  • 170,088
  • 45
  • 397
  • 571
Nicksen782
  • 23
  • 1
  • 6