-1

Unfortunately, I don't get it...

I have a php variable which contains comma separated numbers as a string:

$var = $_POST['postvar'];

And postvar contains the string: 1,2,3,4,5,6,7,8,9,10

I want to use this string in a SQL query:

SELECT * FROM table WHERE id NOT IN ('$var');

But MS SQL ignore my id-restriction.

Where is the mistake?

Thank you so much!

jarlh
  • 42,561
  • 8
  • 45
  • 63
ThisIsIT
  • 1
  • 1
  • `WHERE id NOT IN ('1,2,3,4,5,6,7,8,9,10');` will look for an integer in a list consisting of one string literal. – jarlh Aug 03 '18 at 07:44
  • 1
    **Warning:** You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized [Prepared Statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead of manually building your queries like that. Specially since you're not escaping the user inputs at all! – M. Eriksson Aug 03 '18 at 07:48
  • When using `IN()` with prepared statements, you need to dynamically create the placeholders. Here's one answer about that: https://stackoverflow.com/questions/3703180/a-prepared-statement-where-in-query-and-sorting-with-mysql. That's for MySQL, but the principle is the same (and I don't know what mssql-api you're using). – M. Eriksson Aug 03 '18 at 07:50

1 Answers1

1

Dont use concatenation in SQL querys!

Firstly either check each value is an int or cast it, for example:

<?php

$var = '1,2,3,4,5,6,7,8,9,10,11);DROP table users;--';

$var = explode(',', $var);

array_walk($var, function(&$value){
    $value = (int) $value;
});

print_r($var);

https://3v4l.org/AGRqL

Array
(
    [0] => 1
    [1] => 2
    [2] => 3
    [3] => 4
    [4] => 5
    [5] => 6
    [6] => 7
    [7] => 8
    [8] => 9
    [9] => 10
    [10] => 0
)

Then use prepared queries [sqlsrv_prepare()] after building your query string:

$sql = '
    SELECT * 
    FROM table 
    WHERE id NOT IN (
        '.implode(',', array_fill(0, count($var), '?')).'
    )
';

echo $sql;

https://3v4l.org/oU6iB

Lawrence Cherone
  • 46,049
  • 7
  • 62
  • 106