SQL query doesn't execute on MSSQL

Question

The script receives variable from URL:

if(isset($_GET['string'])){
    $string = $_GET['string'];
}

Then I use this variable in sql query:

$sql = 
"SELECT 
    *
FROM 
    mytable
WHERE
    mytable.column_a = '".$string."'";

The problem is that this query doesn't execute, where my variable contains special characters. Example:

/myscript.php?string=a>xxx<P>yy@"

Tried to use both htmlentities() and addslashes(). Also tried to copy/paste echo of the variable - works fine.

How can I solve this problem?

Use query parameters instead of stuffing the value into the query string. — Gordon Linoff
– Gordon Linoff, Commented Aug 29, 2016 at 10:57
I would also suggest the same thing, use query parameters instead of putting the values in your php query string — Surajit Biswas
– Surajit Biswas, Commented Aug 29, 2016 at 11:00
If you have special characters and you take the string from URL, dont you need to use decoding? php.net/manual/en/function.htmlspecialchars-decode.php — inubs
– inubs, Commented Aug 29, 2016 at 11:00
either you use parameters which will fix it for you, or you write a few pages of code to escape every special character. — GuidoG
– GuidoG, Commented Aug 29, 2016 at 11:01

gofr1 · Accepted Answer · 2016-08-29 12:39:30Z

1

Please, use parameters instead of concatenate query parts. This code should work fine:

<?php
header('Content-Type: text/html; charset=utf-8');

$serverName = "SERVER\INSTANCE";
$connectionInfo = array("Database"=>"Test");
$conn = sqlsrv_connect($serverName, $connectionInfo);

if(isset($_GET['string'])){
    $params = array($_GET['string']);
}

if( $conn === false ) {
   echo "Unable to connect.</br>";
   die(print_r(sqlsrv_errors(), true));
}

$tsql = 
"SELECT  *
FROM mytable
WHERE column_a = ?";

$stmt = sqlsrv_query($conn, $tsql, $params);

if( $stmt === false ) {
    echo "Error in executing query.</br>";
    die(print_r(sqlsrv_errors(), true));
}

while ($obj = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) {
    echo $obj[0]; 
}

sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);

answered Aug 29, 2016 at 12:39

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kannan Kandasamy · Accepted Answer · 2016-08-29 11:15:02Z

0

If column_a is nvarchar datatype try including N before the string quotes.

answered Aug 29, 2016 at 11:15

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

Comments

Ashu Jha · Accepted Answer · 2016-08-29 11:57:12Z

0

Try this query

First check $string is getting correct and then try,

$sql = 
"SELECT 
    *
FROM 
    mytable
WHERE
    mytable.column_a = ".$string;

edited Aug 29, 2016 at 11:57

Ashu Jha

3442 silver badges9 bronze badges

answered Aug 29, 2016 at 11:00

kailas

944 bronze badges

Comments

Ashouri · Accepted Answer · 2016-08-29 12:51:57Z

0

I suggest that you use urlencode — URL-encodes ion your codes, for more information and details you can also have a look at following link:

http://php.net/manual/en/function.urlencode.php

answered Aug 29, 2016 at 12:51

Ashouri

8944 silver badges19 bronze badges

Collectives™ on Stack Overflow

SQL query doesn't execute on MSSQL

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related