Text to be stored in database ends before apostrophpe

Question

I have a stored procedure to be stored in database.Like the following:

USE `login`;
DROP procedure IF EXISTS `post_and_fetch_ans`;

DELIMITER $$
USE `login`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `post_and_fetch_ans`(IN answerbody LONGTEXT,IN postid int,IN answerer int)
BEGIN
   INSERT INTO `login`.`answers` (answer_body,userpost_post_id,users_user_id) VALUES (answerbody,postid,answerer) ;
   SELECT * FROM `login`.`answers` WHERE userpost_post_id = postid  ORDER BY answer_date DESC LIMIT 1;
END$$

DELIMITER ;

and to store the text in database i have to stored-procedure inside PDO prepared statement to bind values to the query string.

$result=$db->post_and_fetch("CALL login.post_and_fetch_ans(?,?,?)",array($answer,$post_id,$answerer))->result();

and bind parameters using bindValue method:

public function post_and_fetch($sql,$params=array()){
            $this->_error=false;

             if($conn=$this->_pdo->prepare($sql)){
             $x=1;
                if(count($params)){
                  foreach($params as $param){

                      $conn->bindValue($x,$param);
                      $x++;

                  }

              }
              if($conn->execute()){

                  $this->_results=$conn->fetchAll(PDO::FETCH_OBJ);

                  $this->_count=$conn->rowCount();
                  return $this;

               }else{

                 $this->_error=true;

              }

         }
        }

But when i tried to save a string like

This is a ' test

only

This is a

is stored in database.How i can escape the apostrophe form that text before storing it to database..Any help will be appriciated?

http://stackoverflow.com/questions/2720917/how-to-store-data-which-contains-quotes-in-mysql — crafter, Jan 09 '16 at 08:00

score 0 · Answer 1 · answered Jan 09 '16 at 08:13

0

you can use mysql_real_escape_string on the value before pass to function

 $str = "Zak's and Derick's Laptop";
 $str_with_escape = mysql_real_escape_string($str);

then for decode you must replace the prependents backslashes from the following characters: \x00, \n, \r, \, ', " and \x1a.

answered Jan 09 '16 at 08:13

ScaisEdge

131,976
10
91
107

mysql_real_escape_string is deprecated as of php 7.0. Besides i am not using mysqli either...I am using PDO to get the job done..What will happen if i change the database form mysql to something else? – AL-zami Jan 09 '16 at 08:17
If you don't want a mysql oriented solution you must define a proper string manipulation avoiding the use of specifc db related function.. for encode and decode (a pair of store procedure could be the right solution) looking around i don't find any general solution for pdo in general.. – ScaisEdge Jan 09 '16 at 08:24

Text to be stored in database ends before apostrophpe

1 Answers1