Pesky Apostrophes in Database results

Question

Okay... to make a long story short... here is my code...

<?php 

$con = mysql_connect($db_server_name,$db_username,$db_password);
if (!$con)
  {
  echo "0";
  }
mysql_select_db("" . $db_database_name . "", $con);
$result = mysql_query("SELECT * FROM sched_posts
WHERE user_id='$user_id'");

while($row = mysql_fetch_array($result))
  {
  $post_id = $row['ID'];
  $post_year = $row['post_year'];
  $post_month = $row['post_month'];
  $post_day = $row['post_day'];
  $post_hour = $row['post_hour'];
  $post_minute = $row['post_minute'];
  $post_privacy = $row['post_privacy'];
  $post_message = $row['post_message'];
echo "              {";
echo "                  id: " . $post_id . ",";
echo "                  title: ' " . $post_message . "',";
echo "                  start: new Date(" . $post_year . ", " . $post_month . "-1, " . $post_day . ", " . $post_hour . ", " . $post_minute . "),";
echo "                  allDay: false";
echo "              },";
  }
?>

When returning results, the post_message sometime's comes back with apostrophes in it. How can I get those results to appear as \' instead of just ' (in other words... with a backslash in front of it)?

PS.. I know some of this code looks unnecessary but please try to ignore that.... this is only setup this way for some testing that i am doing for facebook SDK results (for example, the identifiers inside of the WHILE statement).

The problem is, the returned apostrophes are causing the entire thing to go loopy... you know what i mean.

**By building SQL statements with outside variables, you are leaving yourself wide open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. — Andy Lester, Oct 09 '13 at 14:48

score 1 · Answer 1 · answered Oct 09 '13 at 14:44

1

If you convert all those "date partial" columns into a timestamp, you can simply use json_encode():

$ts = mktime($post_hour, $post_minute, 0, $post_month, $post_day, $post_year);

echo json_encode(array(
    'id' => $row['ID'],
    'title' => $row['post_message'],
    'start' => date('r', $ts), // <-- that's a string now
    'allDay' => false,
));

JavaScript has no problems using rfc822 formatted dates.

answered Oct 09 '13 at 14:44

Ja͢ck

170,779
38
263
309

Alternatively, you can simply apply `json_encode()` to individual values. – Álvaro González Oct 09 '13 at 14:51
@ÁlvaroG.Vicario I typically do that in JavaScript using a `.map()` operation, but yes, that would also be possible. – Ja͢ck Oct 09 '13 at 14:52
I tried this but it caused problems again. Perhaps I don't understand how to use json_encode() too well... I even cut and pasted what you showed me. Furthermore, the individual numbers for the date and time sections NEED to be separated as they are the key behind this App. – user2284703 Oct 09 '13 at 14:59
by the way... in this same code, I will need to find a way to truncate the results of post_message to return only 50 character or less in the results. – user2284703 Oct 09 '13 at 15:01
@user2284703 What does "caused problems" mean? Did you forget to put the comma in between the elements? Btw, the separate date fields are used once and then discarded ... how is that different from using `Array.map()` in JavaScript and using `Date.parse()` on the string value? – Ja͢ck Oct 09 '13 at 15:01
by prblems, I mean... the page just didn't show up... no database error message or anything. The javascript is just being used for one thing and that is a calendar display but everything else is using PHP code. – user2284703 Oct 09 '13 at 15:07
I have no idea what Im doing at this point so please dont blame me if my responses sound a bit stupid. Just sayin' – user2284703 Oct 09 '13 at 15:08
@user2284703 I can't really help you with the basics of debugging, unfortunately. – Ja͢ck Oct 09 '13 at 15:08

Will · Accepted Answer · 2013-10-09T15:31:18.163

0

To add backslashes, the function addslashes() would work for this:

http://php.net/manual/en/function.addslashes.php

To encode JSON 100% reliably (especially for fields like this that you can't predict/expect certain values/input), it would be best to use json_encode():

while($row = mysql_fetch_array($result))
{
  $post_id = $row['ID'];
  $post_year = $row['post_year'];
  $post_month = $row['post_month'];
  $post_day = $row['post_day'];
  $post_hour = $row['post_hour'];
  $post_minute = $row['post_minute'];
  $post_privacy = $row['post_privacy'];
  $post_message = $row['post_message'];
  $dateString = ''; // computed date string...
  echo json_encode(array("id"=>$post_id,"title"=>$post_message,"start"=>
  $dateString,"allDay"=>false));
}

edited Oct 09 '13 at 15:31

answered Oct 09 '13 at 14:36

Will

2,343
1
14
14

How did I not know about that? Jeesh... just when you think you know enough about PHP code... some guy comes along and outsmarts you with a simple little thing like that. lol. Thanks. – user2284703 Oct 09 '13 at 14:41
@user2284703 We can't know all the functions... :) It would be pretty cool if we could, though. – Will Oct 09 '13 at 14:42
1

`addslashes()` is one of the most misused functions in PHP. This is not the worse use but, please, use `json_encode()` as Jack suggests. Single quotes are no the only thing that can break JavaScript code. – Álvaro González Oct 09 '13 at 14:50
@ÁlvaroG.Vicario Misused by the uninitiated or not ... you're seriously suggesting that it's incorrect to use a native function to do precisely what the poster was requesting to be done? Also, Javascript wasn't even mentioned here. Q: "How can I get those results to appear as \' instead of just ' (in other words... with a backslash in front of it)" - PHP.net/addslashes: "Returns a string with backslashes before characters that need to be quoted. These characters are single quote ('), double quote ("), backslash (\) and NUL (the NULL byte)." – Will Oct 09 '13 at 15:06
Answering a question ("how to add backslashes") is not the same as solving a problem ("dynamically generate a JavaScript string"). My intuition says this is the classical "what shoe is better to drive a nail" kind of question. – Álvaro González Oct 09 '13 at 15:13
ill just use addslashes() for now and look into these other solutions a bit more later (prior to releasing this). In the meantime, do you know how I can get the post_message to return only 50 characters or less from the results? – user2284703 Oct 09 '13 at 15:14
oh and the only reason I originally asked about the backslashes as a solution is because that's the only thing I could think of at the time. I assumed someone would have a btter solution... but the ones you guys camee up with will consist of me needing to look into it further and make sure those methods will work with other areas of the site as well. – user2284703 Oct 09 '13 at 15:17
@user2284703 Quick and dirty 50 character trim: substr($row['post_message'],0,50); – Will Oct 09 '13 at 15:21
@user2284703 To encode JSON reliably (especially for fields like this that you can't predict/expect certain values/input), yes, definitely follow the json_encode() guidelines. For now, to simply add slashes ... well, just addslashes(). :) – Will Oct 09 '13 at 15:24

score 0 · Answer 3 · answered Oct 09 '13 at 15:08

The json_encode() function is designed to generate JSON data but, since JSON is a subset of JavaScript, it's the best alternative to generate dynamic strings. Here's a use example:

<?php

$post_id = 314;
$post_message = <<<EOM
Jim "Big Boy" O'brian wrote:

<strong>Hi</strong>

EOM;
$post_year = 2013;
$post_month = 10;
$post_day = 9;
$post_hour = 17;
$post_minute = 4;

echo "{";
echo "    id: " . $post_id . ",";
echo "    title: " . json_encode($post_message) . ",";
echo "    start: new Date(" . $post_year . ", " . $post_month . "-1, " . $post_day . ", " . $post_hour . ", " . $post_minute . "),";
echo "    allDay: false";
echo "},";

... that produces:

title: "Jim \"Big Boy\" O'brian wrote:\r\n\r\n<strong>Hi<\/strong>\r\n"

Please note you have to omit the surrounding quotes; the function adds them for you.

Pesky Apostrophes in Database results

3 Answers3