MySQL query with PHP variables Issues

Question

I've scoured the likes of stackoverflow looking for answers, and nothing seems to be working for me. I'm not getting any errors in the /var/log/apache2/error_log, so it seems to be syntactically correct.

I'm making a function page that will take the data from an input form and insert it into the database based on what was filled out by the user to be updated. When I run the query at the mysql CLI it updates it just fine, but it will not work in the page when I run it.

I have multiple queries set up, and they only run based on if the information was filled out, here is an example as if the user wanted to update their first name.

Up top this happens to turn the words the person entered into PHP variables,

$first_name = htmlentities($_POST["first"], ENT_QUOTES);

It also gets the person's user ID from their login information via:

$user_id = $_SESSION['login_info']['user_id'];

Then after that this is where it checks to see if the entity is empty, if it isn't it runs the query.

if ($first_name != "") {
    $result = $dbact->interact("UPDATE person SET first_name = ".$first_name." WHERE idperson = ".$user_id, true);
}

The $result is storing the output of the query, because I'm calling my interact function which looks like this:

public function interact($query,$resultVal) {
    $db = new mysqli("localhost","username","password","calendar");

    if ($db->connect_errno) {
        echo "Failed to connect to MySQL: (" . $db->connect_errno . ") " . $db->connect_error;
    }

    if(!$db) {
        echo "Error: Could not acces database in mySQL";
        exit;
    }

    //Running the query and returning the result based on $resultVal
    if($resultVal) {
        $result = $db->query($query);

        return $result;
    }

    else {
        $db->query($query);
    }
}

It must be something with my syntax, am I concatenating something wrong? It might just be something I'm overlooking, but I would love to have the input.

Thank you all for your time.

When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string concatenation to accomplish this because you have created some severe SQL injection bugs here. Using htmlentities here is completely wrong. That's only relevant when you're displaying that content in an HTML context, such as on a page. — tadman
– tadman, Commented Apr 23, 2014 at 18:38

John Conde · Accepted Answer · 2014-04-23 18:35:32Z

1

You're missing quotes around your string value:

$result = $dbact->interact("UPDATE person SET first_name = ".$first_name." WHERE idperson = ".$user_id, true);
                                                         ^^^^^^^^^^^^^^^^^^^
                                                                HERE

Should be:

$result = $dbact->interact("UPDATE person SET first_name = '".$first_name."' WHERE idperson = ".$user_id, true);

FYI, htmlentities() does not protect you against SQL injections. Your code is vulnerable.

answered Apr 23, 2014 at 18:35

John Conde

220k100 gold badges464 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lindgrenj6 Over a year ago

@John Conde, thank you, I guess I overlooked the littlest things right, I wish I wasn't human some days!

Collectives™ on Stack Overflow

MySQL query with PHP variables Issues

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related