pdo准备转义单引号 [英] pdo prepare escaping single quotes

问题描述

我在正在构建的Web应用程序中使用PDO.我一直认为(实际上我错了)，使用prepare应该有助于在插入的变量中使用单引号，但是似乎我错过了一些东西.插入 L'Aquila 之类的值时，输入数据中有一个单引号时出现错误.

I use PDO in a web application I am building. I always thought (I am wrong actually) that using prepare should help with single quotes in the inserted variables but it seems that I miss something. I get an error inserting values like L'Aquila where there is a single quote in the input data.

我的实际代码是:

        $sql = "INSERT INTO anagrafiche SET
        id_ndg = '$protocol',
        nick = '$nick',
        nome = '$nome',
        cognome = '$cognome',
        ragsoc = '$ragsoc',
        leg_rappr = '$leg_rappr',
        cod_fisc = '$cod_fisc',
        p_iva = '$p_iva',
        cf_estero = '$cf_estero',
        SAE = '$sae',
        RAE = '$rae',
        ATECO = '$ateco',
        CRCODE = '$crcode',
        indirizzo = '$indirizzo',
        civico = '$civico',
        cap = '$cap',
        citta = '$citta',
        prov = '$prov',
        tel = '$tel',
        cell = '$cellulare',
        mail = '$mail',
        note = '$note',
        file_ci = '$file_ci',
        file_cf = '$file_cf',
        file_visura = '$file_visura',
        cittadinanza = '$cittadinanza',
        res_fiscale = '$res_fiscale',
        is_curatore = '$is_curatore',
        is_legale = '$is_legale',
        is_tribunale = '$is_tribunale',
        is_fornitore = '$is_fornitore' ";
    try{
        $s = $pdo->prepare($sql);               
        $s->execute();
    }
    catch (PDOException $e){
        $error = 'Errori nel caricamento: '.$e->getMessage();
    }

，当我尝试加载包含单引号的字符串时，尝试加载字符串 Piazza d'Armi 时出现类似错误 :

and when I try to load a string containing the single quote I get an error like this while trying to load the string Piazza d'Armi :

错误信息:SQLSTATE [42000]:语法错误或访问 违反:1064您的SQL语法有错误；查看手册 对应于您的MySQL服务器版本以获取正确的语法 在'Armi'附近使用，civico ='0'，cap ='83100'，citta ='Avellino'， prov'在第15行

Errori nel caricamento: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Armi', civico = '0', cap = '83100', citta = 'Avellino', prov' at line 15

我想念什么?我认为PDO报价无法为我完成工作，但也许是我不明白这一点

What am I missing? I don't think that PDO quote can do the job for me but maybe it's me that I don't get the point

推荐答案

仅当您对参数化的预处理语句进行参数化时，它才有助于单引号，否则，您所做的只是字符串连接，并且必须正确地形成SQL.

It helps with single quotes only if you do parametrized prepared statements, otherwise all you are doing is string concatenation and have to be subject to properly forming your SQL.

尝试类似的东西:

$sql = "INSERT INTO anagrafiche SET
        id_ndg = :protocol,
        nick = :nick,
        nome = :nome,
        ...
        ";
$params = array(
    ':protocol' => $protocol,
    ':nick' => $nick,
    ':nome' => $nome,
    ...
); 
try{
    $s = $pdo->prepare($sql);               
    $s->execute($params);
} catch (PDOException $e) {
    ...
}

这还为您提供了减轻SQL注入攻击的额外优势.

This also gives you the added advantage of mitigating SQL injection attacks.

如果您想更进一步并强制使用数据类型，可以使用bindValue()或bindParam()

If you want to go a step further and enforce data types, you could use bindValue() or bindParam()

喜欢:

$sql = "INSERT INTO anagrafiche SET
        id_ndg = :protocol,
        nick = :nick,
        nome = :nome,
        ...
        "; 
try{
    $s = $pdo->prepare($sql);
    $s->bindParam(':protocol', $protocol, PDO::PARAM_ST);
    $s->bindParam(':nick', $nick, PDO::PARAM_ST);
    $s->bindParam(':nome', $nome, PDO::PARAM_ST);
    ...
    $s->bindParam(':some_integer', $some_integer, PDO::PARAM_INT);
    ...           
    $s->execute();
} catch (PDOException $e) {
    ...
}

bindValue()的语法与bindParam()相似，但仅在绑定时将变量的值绑定到参数，而不是在语句执行时将变量的值绑定.

bindValue() has similar syntax to bindParam() but only binds the value of the variable at the time of binding to the parameter rather than the value of the variable at the time of statement execution.

这篇关于pdo准备转义单引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！