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

问题描述

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

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

我不知道 $product_type 变量中的值是什么.但是现在，我在 $product_type 变量中得到了 Men's Shirt，这导致了我的 SQL 查询中的语法错误.我确定此错误是由于 Men's Shirt 值中的单引号引起的.我如何根据我的查询转义这个值?以及如何检查我的 $product_type 变量中是否存在 单引号，然后根据我的查询对其进行转义.提前致谢.

解决方案

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

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

这是使用准备好的语句的全部意义.然后你

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.

解决方案

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:

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