Removing voting access for specific object depending on IP Address saved in database

Question

I have tried making a few posts about this problem, but have decided to collect everything in this final one to hopefully somehow solve it.

I am building a site where users can vote on questions from a database. There's no login and so, to make sure everyone can only vote once per question, I am using their IP together with the ID of the question.

First, I get the ID and IP address and store both, making sure they are integers:

if(isset($_GET['id']))
          {

           //Get IP address

           //Test if it is a shared client
           if (!empty($_SERVER['HTTP_CLIENT_IP'])){
            $ip=$_SERVER['HTTP_CLIENT_IP'];

           //Is it a proxy address
           }elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])){
            $ip=$_SERVER['HTTP_X_FORWARDED_FOR'];
           }else{
            $ip=$_SERVER['REMOTE_ADDR'];
           }

           //Save id and IP address as variables
           $id = $_GET['id'];
           $ip_long = ip2long($ip);

I then check to see if the user has already votes, using the two variables. This is where I expect the problem arises. I get a:

Notice: Trying to get property of non-object

from line 116 which is: $row_cnt = $result->num_rows.

Furthermore var_dump ($result) returns bool(false) and var_dump ($row_cnt) returns Null. Adding quotes around the two variables in the query, $ip_long and $id fixes the problem while localhost, but not on my server.

A local var_dump($result) with quotes around the variables returns the following:

object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(1) ["lengths"]=> NULL ["num_rows"]=> int(1) ["type"]=> int(0) }

I would like to add 1 to the QuestionVotes for the specific question and then remove the option to vote on that same question for the specific IP Address.

//Save id and IP address as variables
           $id = $_GET['id'];
           $ip_long = ip2long($ip);

           ///Check to see if user already voted
           $stmt = $conn->prepare("SELECT * FROM User_Votes where UserID = ? and QuestionID = ?");
           mysqli_stmt_bind_param($stmt, 'ss', $ip_long, $id);
           $stmt->execute();
           $result = $stmt->get_result();
            if($result->num_rows){
                //The user has already voted
                echo "Already voted";
            }else{
                //Add IP Address and ID to the User_Votes table
                $stmt = $conn->prepare("INSERT INTO User_Votes (UserID, QuestionID) VALUES (?, ?)");
                mysqli_stmt_bind_param($stmt, 'ss', $ip_long, $id);
                $stmt->execute();
                $stmt = $conn->prepare("UPDATE Question SET QuestionVotes = QuestionVotes + 1 where QuestionID = ?");
                mysqli_stmt_bind_param($stmt, 's', $id);
                $stmt->execute();
            }

       }

And lastly, here is the code I use to build the html boxes containing database question information, add a voting button that displays the current votes and append, what is used as QuestionID, to the url:

// Build 4 question boxes from database Question table, including voting button
      $stmt = $conn->prepare("SELECT * FROM question ORDER BY QuestionVotes DESC LIMIT 4");
      $stmt->execute();

      $result = $stmt->get_result();
      if ($result->num_rows > 0) {
           // output data of each row
           while($row = $result->fetch_assoc()) {
               //$row["QuestionID"] to add id to url
               echo "<div class=\"col-md-3\"><h2>". $row["QuestionHeader"]. "</h2><p>". $row["QuestionText"]. "</p><p><a href=\"index.php?id=". $row["QuestionID"]. "\" class=\"btn btn-success\"> " . $row["QuestionVotes"] . "</a></p></div>";

           }
      }
      else
      {
        echo "0 results";
      }

My tables are as follows:

Question: QuestionID(int11)(pk), QuestionHeader(varchar(20)), QuestionText(text), QuestionVotes(int(5))
User_Votes: UserID(unsigned, int(39)), QuestionID(int(11))

Answer 1

There are couple of things I would like to point out. First, your error:

I get a 'Notice: Trying to get property of non-object' from line 116 which is: $row_cnt = $result->num_rows;.

When you call mysqli->query() with a select query that finds no results then returned object is not an object but instead false.

Second, instead of COUNT(*), just use *.

So to maintain your logic, you should do something like this:

//Check to see if user already voted
$result = $conn->query("SELECT * FROM User_Votes where UserID = '$ip_long' and QuestionID = '$id'");

if ($result === false) { 
    //Add IP Address and ID to the User_Votes table
    $result = $conn->query("INSERT INTO `User_Votes` (`UserID`, `QuestionID`) VALUES ('$ip_long', '$id')");
}elseif($result && $result->num_rows) { 
    //The user has already voted
    echo "Already voted";
}

Edited:

//Check to see if user already voted
$result = $conn->query("SELECT * FROM User_Votes where UserID = '$ip_long' and QuestionID = '$id'");

if($result->num_rows){
    //The user has already voted
    echo "Already voted";
}else{
    //Add IP Address and ID to the User_Votes table
    $result = $conn->query("INSERT INTO User_Votes (UserID, QuestionID) VALUES ('$ip_long', '$id')");
}

Re-edited:

You have to call $stmt->store_result() after $stmt->execute(). And your $stmt->get_result() is unnecessary here because you're not using the selected data.

Part of a comment from the documentation:

If you do not use mysqli_stmt_store_result( ), and immediatley call this function after executing a prepared statement, this function will usually return 0 as it has no way to know how many rows are in the result set as the result set is not saved in memory yet.

So your code should be like this:

if(isset($_GET['id']) && !empty($_GET['id'])){
    $id = $_GET['id'];
    $ip_long = ip2long($ip);

    //Check to see if user already voted
    $stmt = $conn->prepare("SELECT * FROM User_Votes where UserID = ? and QuestionID = ?");
    $stmt->bind_param('ss', $ip_long, $id);
    $stmt->execute();
    $stmt->store_result();
    if($stmt->num_rows){
        //The user has already voted
        echo "Already voted";
    }else{
        //Add IP Address and ID to the User_Votes table
        $stmt = $conn->prepare("INSERT INTO User_Votes (UserID, QuestionID) VALUES (?, ?)");
        $stmt->bind_param('ss', $ip_long, $id);
        $stmt->execute();
        $stmt = $conn->prepare("UPDATE Question SET QuestionVotes = QuestionVotes + 1 where QuestionID = ?");
        $stmt->bind_param('s', $id);
        $stmt->execute();
    }
}

Sidenote: Please don't mix the procedural and object oriented style of mysqli.

Answer 2

You should check the name of your table.

You use this in one of the queries User_Votes and this user_votes in another one. It might work on your development server, if it's powered by Windows, that are case insensitive, but Linux, which probably powers your production server case-sensitive is.

Check this question for more informations: Are table names in MySQL case sensitive?

Also note, that from the code above, your app looks insecure to SQL injection. You should cast the variables to int, or what do you expect them to be.

Answer 3

Your insert statement is using single quotes to enclose your variables. Those should be double quotes so PHP will interpret your variables to be the values instead of the literal string.

This looks to be the root cause of what's going on. Were you able to verify everything was being written to your database tables correctly before pulling them to work on? Then verify your select statement was pulling the data correctly and what the form the data took?

And jcaran's comment is correct ... some verification of the variables you grab will need to be considered.