Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Aug 2012
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pdo prepare statement

    I am storing text in a database table. Some of the saved text have apostrophes. Using PDO prepare statements I have been able to get them into the table, but when I SELECT them the text is cut off at the apostrophe. Can anyone tell me what I am doing wrong. I want single quotes to be escaped in in the Description field.

    Code:
    <?php
    $config['db'] = array(
    	'host'			=>'localhost',
    	'username'		=>'root',
    	'password'		=>'',
    	'dbname'		=>'userdata'
    );
    	
    
    $db = new PDO('mysql:host=' . $config['db']['host'] . ';dbname=' . $config['db']['dbname'], $config['db']['username'], $config['db']['password']); 
    $db->setATTRIBUTE(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    
    $query = $db->prepare("SELECT test . Link, Image_directory, Description FROM test ORDER BY ID DESC");
    $query->execute(array('Description'));
    $row = $query->fetch(PDO::FETCH_ASSOC);
    
    while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
    $Link = $row['Link'];
    $Image = $row['Image_directory'];
    $Desc = $row['Description'];
    //proper syntax for creating my anchor tag with image inside
    	{
    	echo "<a href=",$Link,">";
    	echo "<img src='";
    	echo $Image;
    	echo "' ";
    	echo "title='";
    	echo $Desc;
    	echo"'>";
    	echo "</a>";
    	}
    }
    //execute query
    ?>
    Last edited by Ax3l; 12-25-2012 at 01:25 AM. Reason: Resolved

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    This isn't an issue with MySQL or PDO.
    The data is intact as you requested it. What you need to do is translate the characters you want to display within the html. Convert it using htmlentities or htmlspecialchars and it should display correctly within the attribute.

  • #3
    New Coder
    Join Date
    Aug 2012
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    This isn't an issue with MySQL or PDO.
    The data is intact as you requested it. What you need to do is translate the characters you want to display within the html. Convert it using htmlentities or htmlspecialchars and it should display correctly within the attribute.
    I've been told I should use a prepare statement instead. It works for the Insert statement (values with single quotes are recorded in the table) but I can't get it to work with Select.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    No no, I mean the data coming out. You don't need to change a thing with the storage, just how you display it. So for example, you would currently have <element title="a title with a " in it"> which of course would give you the "a title with a " as the title and ignore the remaining '" in it" part. You need to effectively make it so HTML can still render it but not to misinterpret it as a part of the HTML. So you want that to say <element title="a title with a &quot in it"> which will render correctly in the HTML. So in order to do this you simply print out the results of the description wrapped in the htmlentities or htmlspecialchars functions (both will work for single and double quotes).

    Since you are using a technology that allows for prepared statements and you are accepting data from the user, definitely make use of the prepared statement.

  • #5
    New Coder
    Join Date
    Aug 2012
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    No no, I mean the data coming out. You don't need to change a thing with the storage, just how you display it. So for example, you would currently have <element title="a title with a " in it"> which of course would give you the "a title with a " as the title and ignore the remaining '" in it" part. You need to effectively make it so HTML can still render it but not to misinterpret it as a part of the HTML. So you want that to say <element title="a title with a &quot in it"> which will render correctly in the HTML. So in order to do this you simply print out the results of the description wrapped in the htmlentities or htmlspecialchars functions (both will work for single and double quotes).

    Since you are using a technology that allows for prepared statements and you are accepting data from the user, definitely make use of the prepared statement.
    I've gotten it to work. Thank you for your help. I ended up using htmlspecialchars. Was this what you suggested?
    Code:
    $str = $Desc;
    //proper syntax for creating my anchor tag with image inside
    	{
    	echo "<a href=",$Link,">";
    	echo "<img src='";
    	echo $Image;
    	echo "' ";
    	echo "title='";
    	echo htmlspecialchars($str, ENT_QUOTES);
    	echo"'>";
    	echo "</a>";
    	}


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •