-1

Php Select Statement works with id(with unique values) as record selector but will not work if I use a different column(with unique values) as a selector

THIS WORKS

$Idart = "4";
$sql2 = "SELECT * FROM articles where id in ({$Idart})";
$results2 = $conn->query($sql2);
$row2 = $results2->fetch_assoc();

THIS DOES NOT WORK

$Idart = "5-6142-8906-6641";
$sql2 = "SELECT * FROM articles where IDStamp in ({$Idart})";
$results2 = $conn->query($sql2);
$row2 = $results2->fetch_assoc();

I've tried a variety of different things with MYSQL including deleting "id" column and making "IDStamp" the primary key. Any thoughts appreciated.

Spaceman
  • 29
  • 7
  • casing, the DB is case sensitive. Without seeing the table schema it's hard to say. Also "Will not work" is that it just doen't return expected values, or you get errors. – ArtisticPhoenix Jun 25 '16 at 19:16
  • What is the error message? Have you tried running your query manually on the database to verify that it works as expected without using PHP? – nthall Jun 25 '16 at 19:21

3 Answers3

0

Because You are putting string in IN statement, without quotes.

$sql2 = "SELECT * FROM articles where IDStamp in ('{$Idart}')";

Should work. However, You should look into prepare statements

Bogdan Kuštan
  • 5,427
  • 1
  • 21
  • 30
0

Try adding quotes around the values in the second statement: where IDStamp in ("'". $value."'")

IzzEps
  • 582
  • 6
  • 20
0

AS @Bogdan Kuštan mentions in his answer, use prepared statements. Don't pick this as the correct answer, this is just an example of how to convert it to use prepared statements ( with mysqli, personally I prefer PDO ), as others have answered correctly before me.

$Idart = "5-6142-8906-6641";
$sql2 = "SELECT * FROM articles where IDStamp in ( ? )";

if ($stmt = $conn->prepare($sql2)) {
    $stmt->bind_param("s", $Idart);
    $stmt->execute();
    $row2 = $stmt->fetch_assoc();
}

It's essential in today's world to use prepared statements. There are just to many script kiddies out there looking to hack websites. Even in development and "practice". Practice it the correct way.

By putting $Idart right into the query someone could use this for the value, '); Drop Table articles; -- and wipe out your database table. This works because your then query becomes this:

       "SELECT * FROM articles where IDStamp in (''); Drop Table articles; --')"

Comments in SQL are done using -- so the end is ignored. This allows us to complete the first query '); inject a second one, and comment out the remainder of the first query. It's really not worth the risk for 2 or 3 lines of code.

ArtisticPhoenix
  • 21,464
  • 2
  • 24
  • 38
  • Thank you for the added information. Will make modifications as suggested – Spaceman Jun 25 '16 at 19:58
  • Maybe i should ask this as a new question. If I sanitized the variable from allowing such characters as you gave in your example '); would that also work? I ask because I have some older code for other clients written using mysql statements it seems to be a quicker fix than rewriting the statements for new mysqli or pdo. – Spaceman Jun 25 '16 at 20:27
  • No need to respond to my previous comment. Based on what I have learned from reading it is not enough to sanitize input. – Spaceman Jun 25 '16 at 21:25
  • in mysql you should use mysql_real_escape_string on variables, that said I would rewrite them as the mysql_* family of functions are depreciated and will be removed as of PHP7 – ArtisticPhoenix Jun 25 '16 at 21:50