escaped special characters PDO MySQL

Hi,

I'm wondering how I can avoid the display of the escaped characters like ' (quote), \ (backslash), etc when I'm using PHP PDO for MySQL.

Here is some of the code when I save and INSERT:
$data = array($username, $encoded, $email, time(), '0', $promo);
            $stmt = $this->oDB_ut_trust->prepare('INSERT INTO user
            (username, md5_pw, email, timestamp, guided, promo)
            VALUES (?,?,?,?,?,?)');
            $stmt->execute($data);

Open in new window


then when I SELECT:
$stmt2 = $this->oDB_ut_trust->prepare('SELECT * FROM user
            WHERE email = ?');
            $data2 = array($email);
            $stmt2->execute($data2);
            $row2 = $stmt2->fetch(PDO::FETCH_ASSOC);

Open in new window


I have magic quotes turned off.

Thank you,
Victor
Victor KimuraSEO, Web DeveloperAsked:
Who is Participating?
 
Slick812Connect With a Mentor Commented:
OK, I read your last post, and like I said before, this DOES NOT HAPPEN for me when I use the PDO with a MySQL driver that supports (has installed) the mysqli package. However, I did some research and At this point I can not see that the PDO adds the \ , but I did see a couple of things about the old version of MySQL that does not have the mysqli installed, and just simulates the prepare and execute commands, but it apparently takes care of the escaping and un-escaping automatically? but I can not test this and do not really know. The only other thing that I saw that was somehow relevant to this was the  PDO::ATTR_EMULATE_PREPARES  and if you set that to false as in -
$dbo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

not really sure what this might do, the default setting is true.

did not have much time, but here is a TEST i did just for this, code below -

$dsn = 'mysql:dbname=junk1';
try {
    $dbo = new PDO($dsn, $user, $password);
    $dbo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = 'UPDATE coolo SET name1=?,user1=? WHERE id = 2';//
    $stat = $dbo->prepare($sql);
    $na = "test's";
    $ur = "it's \\a \"QUOTE\" str";
    $stat->execute(array($na, $ur));
    $sql = 'SELECT id,name1,user1 FROM coolo';
    foreach ($dbo->query($sql) as $row){
        echo 'ID- '.$row['id'].' Name- '.$row['name1'].' user- '.$row['user1'].'<br />';
        if($row['id']==2) if($row['name1']==$na)echo 'GOOd '; else echo 'BAd ';
        }
    $dbo = null;
    } catch (PDOException $e) {
    echo 'PDO failed: '.$e->getMessage();

Open in new window


and this outputs -
ID- 2 Name- test's user- it's \a "QUOTE" str
GOOd

There may or may not be some DEFAULT setting changed for your PDO or the PHP and PECL extension settings, that have this effect, I can get some sort of "SQL syntax error that shows the test's  as test\'s if I leave out a ) or screw up the SQL statement, not sure how that is possible, but I have lost some faith in the PDO driver, because of it, , , and will now try and only use the "REAL" interface by using the proper mysqli calls for prepare and execute stuff
0
 
Ray PaseurCommented:
Try inserting var_dump($data) into the first script after line 4 and before the call to the execute() method.  See what you get, and please post that back here in the code snippet.
0
 
Victor KimuraSEO, Web DeveloperAuthor Commented:
Here is some sample entered data:
Array
(
    [0] => vkimura32
    [1] => 7UNTsxuIM6D2kQlQmmkoq9ZDv0vWSvG_JEVsJTiknd0
    [2] => vkimura@ultratrust.com
    [4] => 0
    [5] => test's
)
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ray PaseurCommented:
It looks like the [5] element has not been escaped.  I would have expected test\'s instead.
0
 
Victor KimuraSEO, Web DeveloperAuthor Commented:
How can I fix this? Doesn't the prepare function do this with PDO? If not, then what am I missing?

Thanks
0
 
Ray PaseurConnect With a Mentor Commented:
I would think so.  Maybe try PDO::quote?
http://us2.php.net/manual/en/pdo.prepare.php
0
 
Slick812Connect With a Mentor Commented:
greetings : vkimura2007, , You do not seem to understand how the prepare and execute of the mysqli (PDO) does it's SQL injection safety methods, There is NO TEXT ESCAPE added to the prepare and execute text strings, the mysqli (PDO) uses TWO separate data transfer channels (pipes) to send first the SQL syntax in the prepare('-') statement, and a second data send (pipe) for the several (or one) execute statements, which have the array of insert data in it. This way, any SQL syntax (OR * AND) or any signalers ( Quotes commas) can not be included in the SQL command syntax statement, that is in any part of the insert data strings or integers. Completely separating the Data from the SQL statement, instead of the old way of lumping all of the SQL statement and Data into ONE string and sending that.
0
 
Victor KimuraSEO, Web DeveloperAuthor Commented:
Okay Slick812, thanks. So how do I resolve my problem?
0
 
Slick812Connect With a Mentor Commented:
OK, , I am not sure what your problem may be? As you say - "I'm wondering how I can avoid the display of the escaped characters like ' (quote), \ (backslash), etc", , , , not sure what you mean, sorry.
When I use the PDO for SELECT with an execute statement like -
$stmt2->execute($data2);

I get exactly the same identical output from a SELECT that I had for the input of the INSERT or UPDATE, in every field (strings-VARCHAR, integers, floats, boolean)  that i put into them, there are no added \ , it is unchanged. So I do not see that there is a problem for display, after it come out of a select?
The point I was trying to make is that because mysqli and PDO with the prepare and execute, you never need to escape any string with \ , to prevent the old type of SQL injection.
0
 
Victor KimuraSEO, Web DeveloperAuthor Commented:
Hi Slick812,

I have this code INSERT:
$data = array($username, $encoded, $email, time(), '0', $promo);
            $stmt = $this->oDB_ut_trust->prepare('INSERT INTO user
            (username, md5_pw, email, timestamp, guided, promo)
            VALUES (?,?,?,?,?,?)');
            $stmt->execute($data);

Open in new window


And this code for the UPDATE:
$stmt2 = $this->oDB_ut_trust->prepare('SELECT * FROM user
            WHERE email = ?');
            $data2 = array($email);
            $stmt2->execute($data2);
            $row2 = $stmt2->fetch(PDO::FETCH_ASSOC);

Open in new window


Ray above suggested this:
Try inserting var_dump($data) into the first script after line 4 and before the call to the execute() method.  See what you get, and please post that back here in the code snippet.

So I had this:
Here is some sample entered data for an INSERT:
Array
(
    [0] => vkimura32
    [1] => 7UNTsxuIM6D2kQlQmmkoq9ZDv0vWSvG_JEVsJTiknd0
    [2] => vkimura@ultratrust.com
    [4] => 0
    [5] => test's
)

Notice the field "promo" has
test's
with the single quote. When I look at my table user I see that this field ended up with an escape character so it looks like
test\'s

What is causing it and how can I prevent that escape character because when I display it with a SELECT statement the backslash is displaying on the page? Is it a settings somewhere in maybe php.ini?

Any help is appreciated. Thanks, Victor
0
 
Ray PaseurCommented:
Is there any chance you have magic quotes enabled?  You can find this in phpinfo();
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_6630-Magic-Quotes-a-bad-idea-from-day-one.html
0
All Courses

From novice to tech pro — start learning today.