Single quote escaping in PHP + MySQL + PDO

Question

I need help with escaping a single quote. I have checked a lot of material out there, but I am not that savvy enough to make use of it. My PHP code is below.

<?php
    $db = new PDO("mysql:host=localhost;dbname=tool", 'root', 'password');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);
    $rne = file_get_contents('/path/export.txt');

    $sql = "
        LOAD DATA INFILE '/path/UCBTexport.txt'INTO TABLE tool_tbl FIELDS TERMINATED BY ',' ( Bug_ID, Date_Tool_Ran, Headline, Submitted_By, Create_Modify_Date, Severity, Status );
        UPDATE `tool_tbl` SET `Release_Notes`= '$rne' WHERE id = LAST_INSERT_ID()
        ";

    $stmt = $db->prepare($sql);
    $stmt->execute();
    $i = 0;

    do {
        $i++;
    }
    while ($stmt->nextRowset())
        ;

    $error = $stmt->errorInfo();
    if ($error[0] != "00000")
    {
        echo "Query $i failed: " . $error[2];
    }
    else
    {
        echo "inserted Successfully";
    }
    die();

Both my queries have text that can occasionally contain all types of special characters. How do I use the mysql_real_escape_string within this code to escape special characters?

Please have a look at this: http://stackoverflow.com/questions/14012642/what-is-the-pdo-equivalent-of-mysql-real-escape-string — Refilon, Feb 20 '15 at 08:49
Dennis. thanks for the link, i did have a look at it before posting here. I dont understand one thing though, if i do not have to escape, why i still have problems inserting text with single quotes. — user1659329, Feb 20 '15 at 08:56
I've updated your code a bit. If this was a direct copy of your existing code, please try to replace it and run it. :) — Refilon, Feb 20 '15 at 09:03
@Dennis PDO construct requires 3 arguments, in your change it reduced to 1. Please set place for closing `"` properly. — u_mulder, Feb 20 '15 at 09:10
@u_mulder my edit is not approved yet, so you don't see it yet, lol :) — Refilon, Feb 20 '15 at 09:11
I do thank you for the effort, but it did not work. I still have the same error. and It did not connect to the DB at all. so i retained the connection part as my original code and kept the other edits. But they do not work. — user1659329, Feb 20 '15 at 09:23

score 0 · Answer 1 · answered Feb 20 '15 at 09:29

Try this one it may help you

<?php
        $db = new PDO("mysql:host=localhost;dbname=tool", 'root', 'password');
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
        $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);
        $rne = file_get_contents('/path/export.txt');

            $sql = <<<SQL
    LOAD DATA INFILE '/path/UCBTexport.txt' INTO TABLE tool_tbl FIELDS TERMINATED BY ',' ( Bug_ID, Date_Tool_Ran, Headline, Submitted_By, Create_Modify_Date, Severity, Status );
    UPDATE `tool_tbl` SET `Release_Notes`= '$rne' WHERE id = LAST_INSERT_ID()
    SQL;

        $stmt = $db->prepare($sql);
        $stmt->execute();
        $i = 0;

           do {
              $i++;
              } 
           while ($stmt->nextRowset());


         $error = $stmt->errorInfo();
          if ($error[0] != "00000")

              { 
            echo "Query $i failed: " . $error[2];
              }
            else
              {
            echo "inserted Successfully";
              }
      die();

Navjot, although your comment did point me in the direction to get it fixed. It was a very simple one, which is what i came here looking for. I just had to add the line $rne = mysql_real_escape_string($rne); — user1659329, Feb 20 '15 at 09:54

adarsh hota · Answer 2 · 2015-02-20T10:01:31.253

0

Try by replacing with this.

$sql = "LOAD DATA INFILE '/path/UCBTexport.txt' INTO TABLE `tool_tbl` FIELDS TERMINATED BY ','( Bug_ID, Date_Tool_Ran, Headline, Submitted_By, Create_Modify_Date, Severity, Status ) ;
SET @last_id = LAST_INSERT_ID();
UPDATE `tool_tbl` SET `Release_Notes`= $rne WHERE id=@last_id";

edited Feb 20 '15 at 10:01

answered Feb 20 '15 at 09:55

adarsh hota

327
11
23

score 0 · Accepted Answer · answered Feb 20 '15 at 10:00

<?php
    $db = new PDO("mysql:host=localhost;dbname="tool", 'root', 'password');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);

    $rne = file_get_contents('/path/export.txt');
    $rne = mysql_real_escape_string($rne); >>>> this is the addition that fixed 

       $sql = "
            LOAD DATA INFILE '/path/UCBTexport.txt'INTO TABLE tool_tbl FIELDS TERMINATED BY ',' ( Bug_ID, Date_Tool_Ran, Headline, Subm
            itted_By, Create_Modify_Date, Severity, Status );
            UPDATE `tool_tbl` SET `Release_Notes`= '$rne' WHERE id = LAST_INSERT_ID()
              ";

$stmt = $db->prepare($sql);
$stmt->execute();
$i = 0;

score 0 · Answer 4 · answered Jul 23 '17 at 17:42

This is a old question but I wrote this answer because the accepted answer use mysql_real_escape_string() that are deprecated.

Can use $stmt->bindParam(":parameter_name", $php_variable, PDO::PARAM_STR); and don't need to escape single quote

    $db = new PDO("mysql:host=localhost;dbname=tool", 'root', 'password');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);
    $rne = file_get_contents('/path/export.txt');

       $sql = "
        LOAD DATA INFILE '/path/UCBTexport.txt'INTO TABLE tool_tbl FIELDS TERMINATED BY ',' ( Bug_ID, Date_Tool_Ran, Headline, Subm
itted_By, Create_Modify_Date, Severity, Status );
UPDATE `tool_tbl` SET `Release_Notes`= :rne WHERE id = LAST_INSERT_ID()
         ";

    $stmt = $db->prepare($sql);
    $stmt->bindParam(":rne", $rne, PDO::PARAM_STR);
    $stmt->execute();

Single quote escaping in PHP + MySQL + PDO

4 Answers4