code

PHP를 사용하여 SQL Server에서 문자열을 이스케이프하는 방법은 무엇입니까?

codestyles 2020. 9. 12. 10:03
반응형

PHP를 사용하여 SQL Server에서 문자열을 이스케이프하는 방법은 무엇입니까?


mysql_real_escape_string()SQL Server 의 대안을 찾고 있습니다. addslashes()내 최선의 선택은 또는 사용할 수있는 또 다른 기능은 무엇입니까?

에 대한 대안 mysql_error()도 유용합니다.


addslashes()완전히 적절하지는 않지만 PHP의 mssql 패키지는 적절한 대안을 제공하지 않습니다. 추악하지만 완전히 일반적인 솔루션은 데이터를 16 진수 바이트 문자열로 인코딩하는 것입니다.

$unpacked = unpack('H*hex', $data);
mssql_query('
    INSERT INTO sometable (somecolumn)
    VALUES (0x' . $unpacked['hex'] . ')
');

추상화되면 다음과 같습니다.

function mssql_escape($data) {
    if(is_numeric($data))
        return $data;
    $unpacked = unpack('H*hex', $data);
    return '0x' . $unpacked['hex'];
}

mssql_query('
    INSERT INTO sometable (somecolumn)
    VALUES (' . mssql_escape($somevalue) . ')
');

mysql_error()동등한 것은 mssql_get_last_message()입니다.


function ms_escape_string($data) {
        if ( !isset($data) or empty($data) ) return '';
        if ( is_numeric($data) ) return $data;

        $non_displayables = array(
            '/%0[0-8bcef]/',            // url encoded 00-08, 11, 12, 14, 15
            '/%1[0-9a-f]/',             // url encoded 16-31
            '/[\x00-\x08]/',            // 00-08
            '/\x0b/',                   // 11
            '/\x0c/',                   // 12
            '/[\x0e-\x1f]/'             // 14-31
        );
        foreach ( $non_displayables as $regex )
            $data = preg_replace( $regex, '', $data );
        $data = str_replace("'", "''", $data );
        return $data;
    }

여기에있는 코드 중 일부는 CodeIgniter에서 추출되었습니다. 잘 작동하고 깨끗한 솔루션입니다.

편집 : 위의 코드 조각에는 많은 문제가 있습니다. 그것이 무엇인지 알기 위해 주석을 읽지 않고 이것을 사용하지 마십시오. 더 나은 방법은 이것을 전혀 사용하지 마십시오. 매개 변수화 된 쿼리는 친구입니다 : http://php.net/manual/en/pdo.prepared-statements.php


쿼리에서 매개 변수를 사용할 수 있는데 왜 이스케이프 처리를해야합니까?!

sqlsrv_query(
    $connection, 
    'UPDATE some_table SET some_field = ? WHERE other_field = ?', 
    array($_REQUEST['some_field'], $_REQUEST['id'])
)

값 매개 변수가 있는지 여부에 관계없이 선택, 삭제, 업데이트에서 올바르게 작동합니다 null. 원칙을 따르십시오-SQL을 연결하지 마십시오. 그러면 항상 안전하며 쿼리를 훨씬 더 잘 읽을 수 있습니다.

http://php.net/manual/en/function.sqlsrv-query.php


You could look into the PDO Library. You can use prepared statements with PDO, which will automatically escape any bad characters in your strings if you do the prepared statements correctly. This is for PHP 5 only I think.


Another way to handle single and double quotes is:

function mssql_escape($str)
{
    if(get_magic_quotes_gpc())
    {
        $str = stripslashes($str);
    }
    return str_replace("'", "''", $str);
}

In order to escape single- and double-quotes, you have to double them up:

$value = 'This is a quote, "I said, 'Hi'"';

$value = str_replace( "'", "''", $value ); 

$value = str_replace( '"', '""', $value );

$query = "INSERT INTO TableName ( TextFieldName ) VALUES ( '$value' ) ";

etc...

and attribution: Escape Character In Microsoft SQL Server 2000


After struggling with this for hours, I've come up with a solution that feels almost the best.

Chaos' answer of converting values to hexstring doesn't work with every datatype, specifically with datetime columns.

I use PHP's PDO::quote(), but as it comes with PHP, PDO::quote() is not supported for MS SQL Server and returns FALSE. The solution for it to work was to download some Microsoft bundles:

After that you can connect in PHP with PDO using a DSN like the following example:

sqlsrv:Server=192.168.0.25; Database=My_Database;

Using the UID and PWD parameters in the DSN didn't worked, so username and password are passed as the second and third parameters on the PDO constructor when creating the connection. Now you can use PHP's PDO::quote(). Enjoy.


An answer from 2009-02-22T121000 by user chaos doesn't fit all queries.

For example, "CREATE LOGIN [0x6f6c6f6c6f] FROM WINDOWS" will give you an exception.

PS: look at the SQL Server driver for PHP, http://msdn.microsoft.com/library/cc296181%28v=sql.90%29.aspx and the sqlsrv_prepare function, which can binds parameters.

PSS: Which also didn't help you with the query above ;)


Warning: This function was REMOVED in PHP 7.0.0.

http://php.net/manual/en/function.mssql-query.php

For anyone still using these mssql_* functions, keep in mind that they have been removed from PHP as of v7.0.0. So, that means you eventually have to rewrite your model code to either use the PDO library, sqlsrv_* etc. If you're looking for something with a "quoting/escaping" method, I would recommend PDO.

Alternatives to this function include: PDO::query(), sqlsrv_query() and odbc_exec()


If you are using PDO, you can use the PDO::quote method.


It is better to also escape SQL reserved words. For example:

function ms_escape_string($data) {
    if (!isset($data) or empty($data))
        return '';

    if (is_numeric($data))
        return $data;

    $non_displayables = array(
        '/%0[0-8bcef]/',        // URL encoded 00-08, 11, 12, 14, 15
        '/%1[0-9a-f]/',         // url encoded 16-31
        '/[\x00-\x08]/',        // 00-08
        '/\x0b/',               // 11
        '/\x0c/',               // 12
        '/[\x0e-\x1f]/',        // 14-31
        '/\27/'
    );
    foreach ($non_displayables as $regex)
        $data = preg_replace( $regex, '', $data);
    $reemplazar = array('"', "'", '=');
    $data = str_replace($reemplazar, "*", $data);
    return $data;
}

I have been using this as an alternative of mysql_real_escape_string():

function htmlsan($htmlsanitize){
    return $htmlsanitize = htmlspecialchars($htmlsanitize, ENT_QUOTES, 'UTF-8');
}
$data = "Whatever the value's is";
$data = stripslashes(htmlsan($data));

For the conversion to get the hexadecimal values in SQL back into ASCII, here is the solution I got on this (using the function from user chaos to encode into hexadecimal)

function hexEncode($data) {
    if(is_numeric($data))
        return $data;
    $unpacked = unpack('H*hex', $data);
    return '0x' . $unpacked['hex'];
}

function hexDecode($hex) {
    $str = '';
    for ($i=0; $i<strlen($hex); $i += 2)
        $str .= chr(hexdec(substr($hex, $i, 2)));
    return $str;
}

$stringHex = hexEncode('Test String');
var_dump($stringHex);
$stringAscii = hexDecode($stringHex);
var_dump($stringAscii);

You could roll your own version of mysql_real_escape_string, (and improve upon it) with the following regular expression: [\000\010\011\012\015\032\042\047\134\140]. That takes care of the following characters: null, backspace, horizontal tab, new line, carriage return, substitute, double quote, single quote, backslash, grave accent. Backspace and horizontal tab are not supported by mysql_real_escape_string.

참고URL : https://stackoverflow.com/questions/574805/how-to-escape-strings-in-sql-server-using-php

반응형