PDO MySQL handling apostrophe (') in names

Question

I'm getting problems with my PDO query as soon as there is an apostrophe inside the name I'm looking for, like D'Angelo. Names without (') inside the name are working fine.

try {
    $dbh = new PDO("mysql:host=$host;dbname=$dbname",$config['DB_USERNAME'],$config['DB_PASSWORD']); 
    foreach($dbh->query("SELECT * from position WHERE spieler='$playername'") as $row) {
        echo ''.$row['pos'].'</td></tr>';
    }
    $dbh = null;
} 
catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

Update:

So, with all the help and hints to prepared statements I was able to get this finally to work.

    $dbh = new PDO("mysql:host=$host;dbname=$dbname",$config['DB_USERNAME'],$config['DB_PASSWORD']);
                $stmt = $dbh->prepare("SELECT * from position WHERE spieler=:player1");
                $stmt->execute(array(":player1" => $player1));
                foreach ($stmt as $row) {
                echo ''.$row['pos'].'</td></tr>';
    }
    $dbh = null;

Your code is vulnerable to [SQL injection](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use [mysqli](https://secure.php.net/manual/en/mysqli.prepare.php) or [PDO](https://secure.php.net/manual/en/pdo.prepared-statements.php) prepared statements with bound parameters as described in [this post](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — , Apr 28 '18 at 09:42
Possible duplicate of [PHP PDO prepared statements](https://stackoverflow.com/questions/1457131/php-pdo-prepared-statements) — mickmackusa, Apr 28 '18 at 09:43

Nick · Answer 1 · 2018-04-28T10:24:11.003

3

That's because when you have a name with a ' in it, your query looks like this:

"SELECT * from position WHERE spieler='D'Angelo'"

which is invalid so $dbh->query is returning false, which is an invalid argument for foreach. To make a quick fix, you need to change the query from

"SELECT * from position WHERE spieler='$playername'"

to

"SELECT * from position WHERE spieler='" . addslashes($playername) . "'"

which will give you

"SELECT * from position WHERE spieler='D\'Angelo'"

As has been pointed out, you'd do better with prepared statements:

$stmt = $dbh->prepare("SELECT * from position WHERE spieler=:playername"
$stmt->execute(array(':playername' => $playername));
while ($row = $dbh->fetch_assoc()) {
    echo ''.$row['pos'].'</td></tr>';
}

edited Apr 28 '18 at 10:24

answered Apr 28 '18 at 09:24

Nick

138,499
22
57
95

Your `prepare()` shouldn't have the $ in playername. – Nigel Ren Apr 28 '18 at 10:00
It can be difficult as unless you can actually run the code, you don't always notice the slight flaws in it. – Nigel Ren Apr 28 '18 at 10:22
@NigelRen as was proved by my just noticing another stupid mistake! :( – Nick Apr 28 '18 at 10:24

score 1 · Answer 2 · answered Apr 28 '18 at 09:29

1

You should use prepared statement.

$stmt = $dbh->prepare("SELECT * from position WHERE spieler= :playername")

if(isset($playername))
{
   $stmt->bindParam(":playername", $playername);
}

$stmt->execute();

answered Apr 28 '18 at 09:29

Hiiro

55
10

Wouldn't you halt the entire query block if not set? Why not put the data in the execute call and save the function call? – mickmackusa Apr 28 '18 at 09:36
@mickmackusa Yes you're right. To be honest I did a copy past of a prepared statement which included a condition encompassing `$stmt` – Hiiro Apr 28 '18 at 10:25

PDO MySQL handling apostrophe (') in names

2 Answers2