0

I was advised to update my code to prevent sql injections. So here is what I have.

VARIABLE FROM URL

$Idarticle = "5-6142-8906-6641";

THIS WORKS - OLD

$sql2 = "SELECT * FROM articles WHERE IDArticle IN ('{$Idarticle}')";
$results2 = $conn->query($sql2);
$row2 = $results2->fetch_assoc();

THIS DOES NOT WORK - NEW

$sql2 = "SELECT * FROM articles WHERE IDArticle IN ( ? )";
if ($stmt = $conn->prepare($sql2)) {
  $stmt->bind_param("s", $Idarticle);
  $stmt->execute();
  $row2 = $stmt->fetch();
}

MY CONNECTION SCRIPT

$conn = new mysqli($servername, $username, $password, $db);

In the second example I get no results(no errors either) verses in the first it finds the correct row. I have read numerous similar questions previously asked and while there may be an answer out there, I did not find it. I also tried some of those answers without any success. I appreciate any help.

UPDATED CODE PER COMMENTS

$sql2 = "SELECT * FROM articles WHERE IDArticle = ?";
if (!$stmt = $conn->prepare($sql2)) {
    if (!$stmt->bind_param("s", $Idarticle));
    echo "error: " . $stmt->error;
    if (!$stmt->execute());
    echo "error: " . $stmt->error;
    $row2 = $stmt->fetch();
}

Still not finding the record / no errors being reported

MY SOLUTION

Having spent close to two days researching and trying to solve this issue, I decided mysqli was at the heart of the problem. Why I am sure this issue does have a solution with mysqli, I ended up moving to PDO. I resisted doing this initially but after a few hours of study, it is in my opinion, as well as many others, far better. Bottom line it now works flawlessly with very few changes. My recommendation, If you are struggling with mysqli, switch to PDO.

A BIG THANK YOU TO THOSE WHO TRIED TO HELP

Spaceman
  • 29
  • 7

3 Answers3

0

BTW: Is there any special reason for using IN? 

"SELECT * FROM articles WHERE IDArticle = ?"
lexx9999
  • 736
  • 3
  • 9
  • 1
    Curly braces aren't helping here. The reason op is using IN is because he's trying to pull records from multiple posts. – Jeff Puckett Jun 25 '16 at 23:54
  • 2
    $Idarticle = "5-6142-8906-6641"; from the question is a single value, not a list. `('{$Idarticle}')` adds braces to the parameter in the working example. – lexx9999 Jun 26 '16 at 00:00
  • Ah, you may be right about the IN. But the curly braces are for php variable expansion, and incorrect in your usage. – Jeff Puckett Jun 26 '16 at 00:06
  • I tried the brackets but throws up an error - can not pass parameter by reference(referring to that line number. As far as IN, that was a remnant from the old script. Be right back, have to walk the dog. – Spaceman Jun 26 '16 at 00:09
  • Ok based on comments I got rid of the brackets and IN. Back to where I started. No records no errors. – Spaceman Jun 26 '16 at 00:41
  • I now have... WHERE IDarticle = ?" – Spaceman Jun 26 '16 at 00:46
  • Still it's not even near to being an answer. – Your Common Sense Jun 26 '16 at 06:37
0

This is your problem:

$row2 = $stmt->fetch();

mysqli_stmt::fetch returns boolean true/false on success, and you're trying to use it as an array for row2

You must bind your results first with mysqli_stmt::bind_result, and then fetch

See this creative answer for how to get an associative array from bind_result

Preferably, if you have the MySQL native driver installed, then you can extract this directly with mysqli_stmt::get_result

Also, you're not checking for statement errors.

if ( !$stmt->bind_param("s", $Idarticle) )
    echo "error: " . $stmt->error;

if ( !$stmt->execute() )
    echo "error: " . $stmt->error;

And you should make sure you're using PHP error reporting.

Community
  • 1
  • 1
Jeff Puckett
  • 37,464
  • 17
  • 118
  • 167
  • `$Idarticle = "5-6142-8906-6641"`; that's no integer ;) but if - you were right. Anyway error checking is a always good practice. Thanks for undodownvote. – lexx9999 Jun 26 '16 at 01:19
  • @lexx LOL you're all over this one! I shouldn't be doing this from my phone. Thanks for catching that. – Jeff Puckett Jun 26 '16 at 01:22
  • Thanks I'll do that and follow up – Spaceman Jun 26 '16 at 01:25
  • So I added error checking and I get no errors and it still is not finding any results. At this point I'm thinking this is a dead horse. It ain't going anywhere. Appreciate your time and thoughts. – Spaceman Jun 26 '16 at 02:32
  • @Spaceman OK, I finally got to a real computer and loaded this up. The problem is really obvious now, and I have updated my answer. – Jeff Puckett Jun 26 '16 at 02:52
  • @Jeff Puckett II thanks for your continued efforts. I'll tackle this with a fresh mind and a cup of coffee in the morning. – Spaceman Jun 26 '16 at 03:23
-1

If $results1/$results2 is not a typo, then the new code is very different, because there must be two independent queries. 

$sql2 = "SELECT * FROM articles WHERE IDArticle IN ( ? )";
if ($stmt = $conn->prepare($sql2)) {
  $stmt->bind_param("s", $Idarticle);
  $stmt->execute();
}

But it's not clear then what was done with $results2 later in the code. row2 might be totally unrelated to

$row2 = $results1->fetch_assoc();
lexx9999
  • 736
  • 3
  • 9
  • Very sorry for the confussion on results1 that was a typo in my question but not in my working example. I corrected the question. – Spaceman Jun 26 '16 at 00:36
  • @spaceman: NP, did you try the =? variant? Otherwise stay almost with the original code but `$IdarticleEscaped=mysqli_real_escape_string($Idarticle); $sql2 = "SELECT * FROM articles WHERE IDArticle IN ('{$IdarticleEscaped}')";` – lexx9999 Jun 26 '16 at 00:49
  • Yes i did try =?. Your solution of sanitizing the variable was my original thought before posting this question, but I figured what the he let me give the other a try. Hours later I'm still thinking. I'm going to put error checking in as suggested by Puckett. – Spaceman Jun 26 '16 at 01:31