Using an apostrophe inside a query

Question

So using prepared statements im trying to query with a '

i have a string called $awayteam wich holds : SSS'18 VR1

When i try to query the DB with above string it wont work....

$conn = new PDO($link, $pdo_username, $pdo_password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("SELECT * FROM clublogo WHERE naam = :name");
//$stmt->bindParam(':name', $awayteam);
$stmt->bindParam(':name', $awayteam, PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->fetchAll();
foreach($result as $row){
$awayclublogo = sprintf($row[pad]);

}
$conn = null;

$awayclublogo will be NULL

However when i do it with just TEXT it does work.

$conn = new PDO($link, $pdo_username, $pdo_password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("SELECT * FROM clublogo WHERE naam = :name");
$stmt->bindValue(':name',  "SSS'18 VR1");
$stmt->execute();
$result = $stmt->fetchAll();
foreach($result as $row){
$awayclublogo = sprintf($row[pad]);

}
$conn = null;

OK So @Devon pointed out that the problem was due to HTML encoding. Told me to look at the source, and sure he was right. SSS'18 VR

im getting the variable from the DB like :

$conn = new PDO($link, $pdo_username, $pdo_password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("SELECT * FROM vrip_uitslag WHERE GameID =     :name");
$stmt->bindParam(':name', $gameid);
$stmt->execute();
$result = $stmt->fetchAll();


foreach($result as $row) { 
$hometeam = sprintf($row[Thuis]);
$awayteam = sprintf($row[Uit]);
}

var_dump($awayteam) will result : string(15) "SSS'18 VR1" i then use $awayteam so i have no clue where the HTML encoding is happening....

I would think that would work but you're better off using a prepared query any way then you wont have to worry about specific characters or malicious users. This thread is on the safety side but shows how to use prepared statements, http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — chris85, May 16 '15 at 15:01
i know about prepared statements, i do use them, also here, this is just for short write on here :) — , May 16 '15 at 15:05
@BeingSunny How do i escape it? i tried a str replace and then feed the query but that doesnt work either... — , May 16 '15 at 15:06
@defiancenl... if you do use prepared statements, you wouldn't have to escape it... — Devon Bessemer, May 16 '15 at 15:09
What exactly is `$conn`? If you are using mysqli then look into [`$mysqli->real_escape_string`](http://php.net/manual/en/mysqli.real-escape-string.php). — Salman A, May 16 '15 at 15:17
@Devon $sth = $conn->prepare('SELECT * clublogo WHERE name = ? '); $sth->execute(array($awayteam)); $red = $sth->fetchAll(); var_dump($red); — , May 16 '15 at 15:20

score 1 · Accepted Answer · answered May 16 '15 at 15:15

1

I think it's been established that this is solved using prepared statements since you're using PHP. This would handle this automatically.

In SQL in general, you can escape it in a couple ways:

Use another apostrophe (single quote) before it:

'SSS''18 VR1'

Use the backslash before it:

'SSS\'18 VR1'

answered May 16 '15 at 15:15

Devon Bessemer

34,461
9
69
95

Hi Devon, i tried prepared but this is what happened ...... $sth = $conn->prepare('SELECT * clublogo WHERE name = ? '); $sth->execute(array($awayteam)); $red = $sth->fetchAll(); var_dump($red); $red is empty ..... – May 16 '15 at 15:21
I cannot edit it for now, since it comes from another website – May 16 '15 at 15:23
The error is you don't have a `FROM`. before `clublogo` – Devon Bessemer May 16 '15 at 15:25

Sai Kiran Sripada · Answer 2 · 2015-05-16T15:51:33.777

0

Double up on single quotes. Add an extra single quote to escape it.

SELECT * FROM clublogo WHERE name = 'SSS''18 VR1'

Use prepared statements if you are using PDO.

$dbh = new PDO("...");
$stmt = $dbh->prepare("SELECT * FROM clublogo WHERE name = :name");
$stmt->bindValue(':name',  "SSS'18 VR1");
$stmt->execute();
$result = $stmt->fetchAll();

You have to use bindParam if you are using a variable.

$dbh = new PDO("...");
$stmt = $dbh->prepare("SELECT * FROM clublogo WHERE name = :name");
$stmt->bindParam(':name', $awayteam, PDO::PARAM_STR);
$awayteam = "SSS'18 VR1";
$stmt->execute();
$result = $stmt->fetchAll();

PDO: Prepared statements

edited May 16 '15 at 15:51

answered May 16 '15 at 15:22

Sai Kiran Sripada

1,149
3
15
30

When i try this it works... however when i replace "SSS'18 VR1" with $awayteam (wich has SSS'18 VR1) it wont work... – May 16 '15 at 15:36
You have to use bindParam instead of bindVaram if you are using a variable. http://php.net/manual/en/pdostatement.bindparam.php – Sai Kiran Sripada May 16 '15 at 15:40
Try this: $stmt->bindParam(':name', $awayteam, PDO::PARAM_STR); – Sai Kiran Sripada May 16 '15 at 15:52
Still results in NULL :/ – May 16 '15 at 15:53
@defiancenl, what is the actual value of $awayteam? and what is actually stored in the database? Print out $awayteam, and don't look at the HTML print out, look at the source. – Devon Bessemer May 16 '15 at 15:55
From your earlier comment, I'm fairly certain you have it html encoded for some reason. – Devon Bessemer May 16 '15 at 15:56
@defiancenl well, look at your code and figure out if you're running htmlentitites on it, if you're retrieving the value like this, then you could run http://php.net/manual/en/function.html-entity-decode.php – Devon Bessemer May 16 '15 at 16:00
@SaiKiranSripada, these functions won't decode single quotes by default. You need the correct flag. – Devon Bessemer May 16 '15 at 16:02
@Devon take a look at the edit, i have no clue where the html encode is happening.... the ' is like ' in the DB. not html encoded – May 16 '15 at 16:03
Search your code base for 'htmlentitites' or 'htmlspecialchars'. I think this has exploded beyond the scope of the original question by now. P.S. I don't see why you need to use sprintf(), but that wouldn't cause the issue. – Devon Bessemer May 16 '15 at 16:05

Using an apostrophe inside a query

2 Answers2