php post: replace single quote with double single for sqlserver query

Question

my $POST value is "WYNN'S". When I create an sql query via Yii2 framework:

$vendor = (new Query()) ->select('VendorNo') ->distinct() ->from('Pm_Item i') ->innerJoin('[PurchPrice] [p]', 'p.ItemNo = i.ItemNo AND p.CompanyKey = i.CompanyKey') ->where($condition)->all();

i get an error:

Incorrect syntax near 'S'. The SQL being executed was: SELECT DISTINCT [TopCode] FROM [PM_Item] WHERE Brand IN( 'WYNN'S')

How can I replace 'WYNN'S' with 'WYNN''S' using php?

UPDATE:

    $condition = '';
    $i = 0;
    foreach ($filters as $filter) {
        if (isset($filter['selected'])) {
            if (strpos( $filter['selected']['value'], "'") !== false) {
                $filter['selected']['value'] =  str_replace("'", "''", $filter['selected']['value']); //preg_replace('/\'/', '\'\'', $filter['selected']['value']);
            }
            if ($i != 0 AND $filter['selected']['value'] != '') {
                $condition .= ' AND i.' . $filter['column'] . ' = \'' . $filter['selected']['value'] . '\'';
            } elseif ($filter['selected']['value'] != '') {
                $condition .= 'i.' . $filter['column'] . ' = \'' . $filter['selected']['value'] . '\'';
            }

            $i++;
        }
    }

I use Yii2 framework: ` $vendor = (new Query()) ->select('VendorNo') ->distinct() ->from('Pm_Item i') ->innerJoin('[PurchPrice] [p]', 'p.ItemNo = i.ItemNo AND p.CompanyKey = i.CompanyKey') ->where($condition)->all();` — Lyuba, Jun 22 '17 at 08:06
@Lyuba please provide full code sample, especially how you construct `$condition` — BNT, Jun 22 '17 at 08:20

score 1 · Answer 1 · answered Jun 22 '17 at 08:21

1

uses addslashes() to add backslash befor special character of string

$str = addslashes("WYNN'S");

then pass to sql query.

answered Jun 22 '17 at 08:21

Ashu

1,320
2
10
24

Ivo P · Answer 2 · 2017-06-22T08:12:54.153

0

the str_replace() function seems to do that:

<?php
  $query = str_replace("'", "''" , $query);
?>

That would replace every occurance of a ' with 2 of them: ''.

edited Jun 22 '17 at 08:12

answered Jun 22 '17 at 08:09

Ivo P

1,722
1
7
18

please note, that while this might work, its always best to thoroughly escape userinput as described at e.g. [here](https://stackoverflow.com/a/130323/7926064) – BNT Jun 22 '17 at 08:13
This does not work, I already wrap WYNN'S into single quotes when I create $condition for sql query, and replacement of single with double does not affect the string – Lyuba Jun 22 '17 at 08:16

score 0 · Answer 3 · answered Jun 22 '17 at 08:22

you should escape the special characters in your sql statements

if you are using the mysqli extention , use the mysqli::real_escape_string to scapes special characters in a string for use in an SQL statement (including the single quote)

// Query
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
            mysql_real_escape_string($user),
            mysql_real_escape_string($password));

as for the PDO extention , use PDO::quote or PDO Prepare

and if you are using the deprecated mysql extension , you can use mysql-real-escape-string

http://php.net/manual/en/function.mysql-real-escape-string.php

example:

$lastname  = "O'Reilly";
$_lastname = mysql_real_escape_string($lastname);
$query = "SELECT * FROM actors WHERE last_name = '$_lastname'";

score 0 · Answer 4 · edited Jun 22 '17 at 08:31

I would prefer to write a escape function and use that for every value in the query:

function escape_sql(string $string) 
{
   return str_replace("'", "''", $string);
}

$query = "SELECT DISTINCT [TopCode] 
          FROM [PM_Item] 
          WHERE  Brand IN( '". escape_sql("WYNN'S"). "')";
?>

Or go for a PDO approach where you could let PHP figure it out using ->prepare() and ->bindValue()

score 0 · Answer 5 · answered Jun 22 '17 at 08:44

0

I noticed only too late you are using yii.

Would the part ->where() not have to be like:

->where(['in', 'Brand', ["WYNN'S"]])

according to http://www.yiiframework.com/doc-2.0/yii-db-query.html#where()-detail this would lead to propper escaping by yii

answered Jun 22 '17 at 08:44

Ivo P

1,722
1
7
18

php post: replace single quote with double single for sqlserver query

5 Answers5