Przekazywanie tablicy do zapytania za pomocą klauzuli WHERE

Biorąc pod uwagę tablicę identyfikatorów $galleries = array(1,2,5), chcę mieć zapytanie SQL, które wykorzystuje wartości tablicy w jej klauzuli WHERE, takie jak:

SELECT *
FROM galleries
WHERE id = /* values of array $galleries... eg. (1 || 2 || 5) */

Jak mogę wygenerować ten ciąg zapytania do użycia z MySQL?

STRZEC SIĘ! Ta odpowiedź zawiera poważną lukę w iniekcji SQL . NIE używaj próbek kodu przedstawionych tutaj, nie upewniając się, że wszelkie zewnętrzne dane wejściowe są dezynfekowane.

$ids = join("','",$galleries);   
$sql = "SELECT * FROM galleries WHERE id IN ('$ids')";

Korzystanie z PDO: ^[1]

$in = join(',', array_fill(0, count($ids), '?'));
$select = <<<SQL
    SELECT *
    FROM galleries
    WHERE id IN ($in);
SQL;
$statement = $pdo->prepare($select);
$statement->execute($ids);

Korzystanie z MySQLi ^[2]

$in = join(',', array_fill(0, count($ids), '?'));
$select = <<<SQL
    SELECT *
    FROM galleries
    WHERE id IN ($in);
SQL;
$statement = $mysqli->prepare($select);
$statement->bind_param(str_repeat('i', count($ids)), ...$ids);
$statement->execute();
$result = $statement->get_result();

Wyjaśnienie:

Użyj IN()operatora SQL , aby sprawdzić, czy wartość istnieje na danej liście.

Ogólnie wygląda to tak:

expr IN (value,...)

Możemy zbudować wyrażenie do umieszczenia wewnątrz ()naszej tablicy. Zauważ, że w nawiasie musi znajdować się co najmniej jedna wartość, inaczej MySQL zwróci błąd; oznacza to upewnienie się, że nasza tablica wejściowa ma co najmniej jedną wartość. Aby zapobiec atakom typu SQL injection, najpierw wygeneruj ?dla każdego elementu wejściowego, aby utworzyć sparametryzowane zapytanie. Tutaj zakładam, że tablica zawierająca twoje identyfikatory nazywa się $ids:

$in = join(',', array_fill(0, count($ids), '?'));

$select = <<<SQL
    SELECT *
    FROM galleries
    WHERE id IN ($in);
SQL;

Podana tablica wejściowa trzech elementów $selectbędzie wyglądać następująco:

SELECT *
FROM galleries
WHERE id IN (?, ?, ?)

Ponownie zauważ, że ?dla każdego elementu w tablicy wejściowej znajduje się znak . Następnie użyjemy PDO lub MySQLi do przygotowania i wykonania zapytania, jak wspomniano powyżej.

Używanie IN()operatora z ciągami znaków

Łatwo jest zmieniać ciągi znaków i liczby całkowite ze względu na związane parametry. W przypadku ChNP zmiana nie jest wymagana; dla MySQLi zmień str_repeat('i',na, str_repeat('s',jeśli chcesz sprawdzić ciągi.

^{[1]: Pominąłem sprawdzanie błędów pod kątem zwięzłości. Musisz sprawdzić typowe błędy dla każdej metody bazy danych (lub ustawić sterownik DB tak, aby generował wyjątki).}

^{[2]: Wymaga PHP 5.6 lub wyższej. Znowu pominąłem pewne sprawdzanie błędów pod kątem zwięzłości.}

ints:

$query = "SELECT * FROM `$table` WHERE `$column` IN(".implode(',',$array).")";

smyczki:

$query = "SELECT * FROM `$table` WHERE `$column` IN('".implode("','",$array)."')";

Zakładając, że wcześniej odpowiednio odkażysz swoje dane wejściowe ...

$matches = implode(',', $galleries);

Następnie dostosuj zapytanie:

SELECT *
FROM galleries
WHERE id IN ( $matches ) 

Podaj wartości odpowiednio w zależności od zestawu danych.

Posługiwać się:

select id from galleries where id in (1, 2, 5);

Działa prosta for eachpętla.

Sposób Flaviusa / AvatarKava jest lepszy, ale upewnij się, że żadna z wartości tablicy nie zawiera przecinków.

Jako odpowiedź Flaviusa Stefa możesz intval()się upewnić, że wszystkie idsą wartościami int:

$ids = join(',', array_map('intval', $galleries));  
$sql = "SELECT * FROM galleries WHERE id IN ($ids)";

Dla MySQLi z funkcją Escape:

$ids = array_map(function($a) use($mysqli) { 
    return is_string($a) ? "'".$mysqli->real_escape_string($a)."'" : $a;
  }, $ids);
$ids = join(',', $ids);  
$result = $mysqli->query("SELECT * FROM galleries WHERE id IN ($ids)");

W przypadku PDO z przygotowanym wyciągiem:

$qmarks = implode(',', array_fill(0, count($ids), '?'));
$sth = $dbh->prepare("SELECT * FROM galleries WHERE id IN ($qmarks)");
$sth->execute($ids);

Powinniśmy zająć się lukami w iniekcji SQL i pustym stanem . Zajmę się obydwoma jak poniżej.

Dla czystego tablicy numerycznej użyciu odpowiedniego mianowicie konwersji typu intvallub floatvallub doublevalnad każdym elementem. Dla typów ciągów, mysqli_real_escape_string()które można również zastosować do wartości liczbowych, jeśli chcesz. MySQL dopuszcza zarówno liczby, jak i warianty dat jako ciąg znaków .

Aby odpowiednio uniknąć wartości przed przekazaniem do zapytania, utwórz funkcję podobną do:

function escape($string)
{
    // Assuming $db is a link identifier returned by mysqli_connect() or mysqli_init()
    return mysqli_real_escape_string($db, $string);
}

Taka funkcja najprawdopodobniej byłaby już dostępna w twojej aplikacji, a może już ją stworzyłeś.

Wyczyść tablicę ciągów, np .:

$values = array_map('escape', $gallaries);

Tablica numeryczna może być zdezynfekowana za pomocą intvallub floatvallub doublevalzamiast tego odpowiednio:

$values = array_map('intval', $gallaries);

Następnie w końcu zbuduj warunek zapytania

$where  = count($values) ? "`id` = '" . implode("' OR `id` = '", $values) . "'" : 0;

lub

$where  = count($values) ? "`id` IN ('" . implode("', '", $values) . "')" : 0;

Ponieważ tablica może być czasami pusta, $galleries = array();należy zauważyć, że IN ()nie zezwala na pustą listę. Można również użyć ORzamiast tego, ale problem pozostaje. Tak więc powyższa kontrola count($values)ma zapewnić to samo.

I dodaj go do końcowego zapytania:

$query  = 'SELECT * FROM `galleries` WHERE ' . $where;

TIP : Jeśli chcesz wyświetlić wszystkie rekordy (bez filtrowania) w przypadku pustej tablicy zamiast ukrywając wszystkie wiersze, po prostu wymień 0 z 1 w trójoperandowy fałszywej strony.

Bezpieczniej

$galleries = array(1,2,5);
array_walk($galleries , 'intval');
$ids = implode(',', $galleries);
$sql = "SELECT * FROM galleries WHERE id IN ($ids)";

Biblioteka SafeMySQL płk Shrapnela dla PHP zawiera sparametryzowane symbole zastępcze w sparametryzowanych zapytaniach i zawiera kilka wygodnych symboli zastępczych do pracy z tablicami. Symbol ?azastępczy rozwija tablicę do rozdzielonej przecinkami listy ciągów znaków *.

Na przykład:

$someArray = [1, 2, 5];
$galleries = $db->getAll("SELECT * FROM galleries WHERE id IN (?a)", $someArray);

* Zauważ, że ponieważ MySQL wykonuje automatyczny przymus typu, nie ma znaczenia, że ​​SafeMySQL skonwertuje powyższe identyfikatory na ciągi - nadal otrzymasz poprawny wynik.

Możemy użyć tej klauzuli „WHERE id IN”, jeśli odpowiednio przefiltrujemy tablicę wejściową. Coś takiego:

$galleries = array();

foreach ($_REQUEST['gallery_id'] as $key => $val) {
    $galleries[$key] = filter_var($val, FILTER_SANITIZE_NUMBER_INT);
}

Podobnie jak w poniższym przykładzie:

$galleryIds = implode(',', $galleries);

Tj. Teraz powinieneś bezpiecznie używać $query = "SELECT * FROM galleries WHERE id IN ({$galleryIds})";

Możesz mieć stolik texts (T_ID (int), T_TEXT (text))i stoliktest (id (int), var (varchar(255)))

W insert into test values (1, '1,2,3') ;następujących wierszy wyjściowe będzie z tekstami stole, gdzie T_ID IN (1,2,3):

SELECT * FROM `texts` WHERE (SELECT FIND_IN_SET( T_ID, ( SELECT var FROM test WHERE id =1 ) ) AS tm) >0

W ten sposób możesz zarządzać prostą relacją bazy danych n2m bez dodatkowej tabeli i używając tylko SQL bez potrzeby używania PHP lub innego języka programowania.

Więcej przykładu:

$galleryIds = [1, '2', 'Vitruvian Man'];
$ids = array_filter($galleryIds, function($n){return (is_numeric($n));});
$ids = implode(', ', $ids);

$sql = "SELECT * FROM galleries WHERE id IN ({$ids})";
// output: 'SELECT * FROM galleries WHERE id IN (1, 2)'

$statement = $pdo->prepare($sql);
$statement->execute();

Oprócz użycia zapytania IN masz do tego dwie opcje, ponieważ w zapytaniu IN istnieje ryzyko podatności na iniekcję SQL. Możesz użyć pętli, aby uzyskać dokładne dane, lub możesz użyć zapytania z literą OR

1. SELECT *
      FROM galleries WHERE id=1 or id=2 or id=5;


2. $ids = array(1, 2, 5);
   foreach ($ids as $id) {
      $data[] = SELECT *
                    FROM galleries WHERE id= $id;
   }

Bezpieczny sposób bez PDO:

$ids = array_filter(array_unique(array_map('intval', (array)$ids)));

if ($ids) {
    $query = 'SELECT * FROM `galleries` WHERE `id` IN ('.implode(',', $ids).');';
}

• (array)$idsRzuć $idszmienną na tablicę
• array_map Przekształć wszystkie wartości tablic na liczby całkowite
• array_unique Usuń powtarzające się wartości
• array_filter Usuń wartości zerowe
• implode Połącz wszystkie wartości z wyborem IN

Ponieważ oryginalne pytanie dotyczy tablicy liczb i używam tablicy ciągów, nie mogłem sprawić, by podane przykłady działały.

Odkryłem, że każdy ciąg musi być zamknięty w pojedynczych cudzysłowach, aby pracować z IN()funkcją.

Oto moje rozwiązanie

foreach($status as $status_a) {
        $status_sql[] = '\''.$status_a.'\'';
    }
    $status = implode(',',$status_sql);

$sql = mysql_query("SELECT * FROM table WHERE id IN ($status)");

Jak widać, pierwsza funkcja otacza każdą zmienną tablicową, single quotes (\')a następnie wszczepia tablicę.

UWAGA: $statusnie zawiera pojedynczych cudzysłowów w instrukcji SQL.

Prawdopodobnie jest lepszy sposób dodawania cytatów, ale to działa.

Poniżej znajduje się metoda, której użyłem, używając PDO z nazwanymi symbolami zastępczymi dla innych danych. Aby pokonać iniekcję SQL, filtruję tablicę, aby zaakceptować tylko wartości, które są liczbami całkowitymi, i odrzucam wszystkie inne.

$owner_id = 123;
$galleries = array(1,2,5,'abc');

$good_galleries = array_filter($chapter_arr, 'is_numeric');

$sql = "SELECT * FROM galleries WHERE owner=:OWNER_ID AND id IN ($good_galleries)";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(
    "OWNER_ID" => $owner_id,
));

$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

Podstawowe metody zapobiegania wstrzykiwaniu SQL:

• Używaj przygotowanych instrukcji i sparametryzowanych zapytań
• Unikanie znaków specjalnych w niebezpiecznej zmiennej

Użycie przygotowanych instrukcji i sparametryzowanych zapytań jest uważane za lepszą praktykę, ale jeśli wybierzesz metodę znaków specjalnych, możesz wypróbować mój przykład poniżej.

Możesz wygenerować zapytania, array_mapdodając pojedynczy cytat do każdego z elementów w $galleries:

$galleries = array(1,2,5);

$galleries_str = implode(', ',
                     array_map(function(&$item){
                                   return "'" .mysql_real_escape_string($item) . "'";
                               }, $galleries));

$sql = "SELECT * FROM gallery WHERE id IN (" . $galleries_str . ");";

Wygenerowany $ sql var będzie:

SELECT * FROM gallery WHERE id IN ('1', '2', '5');

Uwaga: mysql_real_escape_string , jak opisano w jego dokumentacji tutaj , był przestarzały w PHP 5.5.0 i został usunięty w PHP 7.0.0. Zamiast tego należy użyć rozszerzenia MySQLi lub PDO_MySQL. Zobacz także MySQL: wybór przewodnika po interfejsie API i powiązane FAQ, aby uzyskać więcej informacji. Alternatywy dla tej funkcji obejmują:

• mysqli_real_escape_string ()

• PDO :: quote ()