如何使用PHP在字符串中转义单引号(撇号) [英] How to escape single-quote (apostrophe) in string using php

问题描述

我有一个这样的SQL查询:-

I have a SQL query like this:-

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

我不知道 $ product_type 变量的值是多少.但是现在，我在 $ product_type 变量中得到了男式衬衫，这在我的SQL查询中引起了语法错误.我确定此错误是由于男士衬衫值中的单引号引起的.如何根据我的查询转义该值?以及如何检查我的 $ 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.

推荐答案

答案是您不需要这样做.使用PDO的prepare的正确方法是这样的:

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)

证明

模式:

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');

查看数据:

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();
    }
?>

浏览器:

这篇关于如何使用PHP在字符串中转义单引号(撇号)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！