How to escape single-quote (apostrophe) in string using php

Question

I have a SQL query like this:-

$stmt = $pdo->prepare(
   "SELECT * FROM `products_keywords` WHERE `product_type` = '" . $product_type . "' ");

I don't know what will be the value in the $product_type variable. But Now, I am getting Men's Shirt in $product_type variable which is causing the syntax error in my SQL query. I am sure this error is due to the single quote in Men's Shirt value. How I escape this value according to my query? And how to check if there is single quote in my $product_type variable and then escape it according to my query. Thanks in advance.

Assign **Men's Shirt** to a PHP `Variable` and use it. Like `$prod = "Men's Shirt";` — Vignesh Chinnaiyan, Sep 22 '16 at 09:19
It seems you missed the whole point of [prepared SQL statements](http://php.net/manual/en/pdo.prepare.php). One of its purposes is to avoid generating SQL queries by string concatenation. — axiac, Sep 22 '16 at 09:19
@RakeshSojitra thanks for reply. can you please more elaborate? and edit my query according to question. Thanks. — junaid afzal, Sep 22 '16 at 09:20
$query = $link->prepare('SELECT * FROM `products_keywords` WHERE `product_type` = :product_type'); $query->execute([':product_type' => $product_type]); # No need to escape it! — Alive to die - Anant, Sep 22 '16 at 09:20
I voted to reopen this question, after it was closed as a duplicate of http://stackoverflow.com/questions/60174/. The questions are clearly not the same, even if the answers are similar. — Bill Karwin, Sep 27 '16 at 14:32
@junaidafzal, please remember to upvote answers that were helpful to you, and mark the accepted checkmark on the answer that helped you the best. — Bill Karwin, Sep 27 '16 at 14:33

score 8 · Answer 1 · edited Sep 22 '16 at 15:55

8

The answer is that you don't need to. The proper way to use PDO's prepare is like this:

$stmt = $pdo->prepare(
   "SELECT * FROM `products_keywords` WHERE `product_type` = ?");

This is the whole point of using a prepared statement. Then you bind the parameter as follows:

$stmt->bindParam(1, $product_type)

Proof,

Schema:

create table `products_keywords`
(   `id` int not null,
    `products_keywords` varchar(1000) not null,
    `product_type` varchar(100) not null
);
insert `products_keywords` (`id`,`products_keywords`,`product_type`) values  
(1,'zoom lawn cut mower',"Lawn Mower"),
(2,'stylish torso Polo','Men\'s Shirt');

View data:

select * from `products_keywords`;
+----+---------------------+--------------+
| id | products_keywords   | product_type |
+----+---------------------+--------------+
|  1 | zoom lawn cut mower | Lawn Mower   |
|  2 | stylish torso Polo  | Men's Shirt  |
+----+---------------------+--------------+

PHP:

<?php
    // turn on error reporting, or wonder why nothing is happening at times
    error_reporting(E_ALL);
    ini_set("display_errors", 1);    

    $servername="localhost";
    $dbname="so_gibberish";
    $username="nate123";
    $password="openSesame1";

    try {
        $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

        $product_type="Men's Shirt";
        $stmt = $pdo->prepare("SELECT * FROM `products_keywords` WHERE `product_type` = ?");
        $stmt->bindParam(1, $product_type);
        $stmt->execute();
        while($row = $stmt->fetch()) {
            echo $row['id'].", ".$row['products_keywords'].", ".$row['product_type']."<br/>";
        }
    } catch (PDOException $e) {
        echo 'pdo problemo: ' . $e->getMessage();   // dev not production code
        exit();
    }
?>

Browser:

edited Sep 22 '16 at 15:55

Drew

24,851
10
43
78

answered Sep 22 '16 at 09:20

e4c5

52,766
11
101
134

How use this in like clause with wildcard? – junaid afzal Sep 22 '16 at 09:32
just use `where product_type like ?` – e4c5 Sep 22 '16 at 09:41
why don't you just try?? – e4c5 Sep 22 '16 at 09:44
can you please give me escape character solution please? – junaid afzal Sep 22 '16 at 10:21
2

No I will not because that's not the right way to use PDO. Then you might has well start using mysql_* functions – e4c5 Sep 22 '16 at 10:22
@junaidafzal Just use `LIKE` as normal in your SQL query as suggested by e4c5 and add the wildcard(s) to your parameter string; e.g: `$stmt->bindParameter(1, '%' . $product_type . '%');` – Darragh Enright Sep 22 '16 at 15:42
+1 But you don't even need to use bindParam(). You can just pass an array to execute(). I don't understand why so many PHP developers think bindParam() is necessary. – Bill Karwin Sep 22 '16 at 15:50
Or `$stmt->execute(['%' . $product_type . '%']);` as @BillKarwin quite rightly points out ;) – Darragh Enright Sep 22 '16 at 16:14
Or even `$stmt->execute(["%$product_type%"]);` I don't know why people use the ugly string-concat syntax when you can just put a variable into a double-quoted string. – Bill Karwin Sep 22 '16 at 17:07
@drew I think you have the golden vote that can close without waiting for the others! Interesting thing about dupes coming up ini the chat, I didn't know about it. Guess i should drop in there sometimes. Thanks for this and all the other great things you have done – e4c5 Sep 23 '16 at 00:07

score 2 · Answer 2 · answered Sep 22 '16 at 09:22

2

I would actually suggest doing it the following way:

$stmt = $pdo->prepare(
   'SELECT * FROM `products_keywords` WHERE `product_type` = :product_type');
$res = $stmt->execute(array(':product_type' => $product_type));

This way you don't need to escape anything and your query is safe.

answered Sep 22 '16 at 09:22

jakub wrona

2,212
17
17

How use this in like clause with wildcard? – junaid afzal Sep 22 '16 at 09:31
$res = $stmt->execute(array(':product_type' => $product_type.'%')); and in the query string replace "equals character" with a "LIKE" – jakub wrona Sep 22 '16 at 09:40
can you please give me escape character solution please? – junaid afzal Sep 22 '16 at 10:20

score 2 · Answer 3 · edited Oct 16 '17 at 02:06

2

In order to insert a quote in a PDO statement, just use the addslashes function. For example:

function insert($tagName)
{
    $tagName = addslashes($tagName);
    $rs = $this->db->prepare("INSERT INTO tags (tagName) VALUES ('$tagName')");
    $rs->execute();
}

edited Oct 16 '17 at 02:06

Dekker1

5,565
25
33

answered Oct 15 '17 at 22:22

user8781384

21
1

score 0 · Answer 4 · answered Sep 22 '16 at 15:56

You keep asking for a solution to escape the apostrophe. I agree with the other comments and answers that you should get used to using query parameters. It's cleaner and easier.

But PDO does actually have a function for escaping. It's called PDO::quote():

$product_type_quoted = $pdo->quote($product_type);

$stmt = $pdo->prepare(
   "SELECT * FROM `products_keywords` 
    WHERE `product_type` = $product_type_quoted");

Notice that quote() escapes the string as needed, and also adds single-quotes around it, so you don't need to do that when you use that variable in your SQL string.

Read the documentation for more information: http://php.net/manual/en/pdo.quote.php In fact, you could have answered this question yourself by spending a few minutes reading the PDO documentation.

How to escape single-quote (apostrophe) in string using php

4 Answers4

Linked

Related