PHP Update query is being executed, but the credentials are not updated

Question

I have a form tag on my site that leads to the PHP page with email and/or/without description. Based on that the code generates a query, the query needs to update these credidentials. That part of the code works and has been tested. The problem is that the database is not updating the e-mail credidential, but if i put it to update the description it does so. The code has 3 checks, if the user puts only his email, if he puts only his description or puts both. Based on that the code works like this :

<?php
session_start();
include_once 'connection.php';
$id = $_SESSION['user_id'];
if(isset($_POST['emailChange']) || isset($_POST['descChange'])){
       $desc = $_POST['descChange'];
       $email = $_POST['emailChange'];
       if(empty($email)){
           $query = "UPDATE users SET description = :descr WHERE user_id= :id ;";
           $stmt = $conn->prepare($query);
           $stmt->bindParam(":descr", $desc);
       } else if(empty($desc)){
           $query = "UPDATE users SET user_email= :email WHERE user_id= :id ;";
           $stmt = $conn->prepare($query);
           $stmt->bindParam(":email", $email);
       } else{
           $query = "UPDATE users SET description = :descr AND user_email = :email WHERE user_id= :id;";
           $stmt = $conn->prepare($query);
           $stmt->bindParam(":email", $email);
           $stmt->bindParam(":descr", $desc);
       }
       if(!filter_var($email, FILTER_VALIDATE_EMAIL)){
           header("Location: ../profile.php?error=invalidEmail");
           exit();
       }
           $stmt->bindParam(":id", $id);
           $stmt->execute();
   }

The form itself looks like this :

 <form action="assets/upload.php" method="POST">
     <input type="text" name="emailChange" class="inputs" id="changeEmail" placeholder = "Enter your new E-mail">
     <input type="text" name="descChange" class="inputs" id="changeDesc" placeholder="Enter your description">
     <button type="submit" id="btnconfirmCreds" name="changeCreds">Confirm Changes</button>
 </form>

The names in the database looks like this :

[user_id][user_username][user_email][user_password][role_id][user_image][description][num_of_posts]

2) Read your [PHP error logs](https://stackoverflow.com/questions/5127838/where-does-php-store-the-error-log-php5-apache-fastcgi-cpanel) — Martin, Apr 19 '19 at 20:22
The connection.php works fine, i used it to create a fully working login system, there are no errors presented — Coa 24, Apr 19 '19 at 20:22
Hmmm, have you set up [PDO error logging correctly](https://stackoverflow.com/questions/8776344/how-to-view-query-error-in-pdo-php)? — Martin, Apr 19 '19 at 20:26
obviously your code DOES NOT work if user does not provide email (but only descr) as it redirects to error page and exits the script — Nikos M., Apr 19 '19 at 20:30
The code works if the email is not provided, the query is not being updated, for some odd reason my email has been updated, the part of the code where it executes with the empty description works. The other 2 parts do not, i assume it's because it views the description as a special word, if that is true then i should change the name in my database. Thoughts? Edit : Sorry for missing your message Martin, it's int(11) auto_increment — Coa 24, Apr 19 '19 at 20:34
@Martin i forgot to tag you, the information is in my previous message — Coa 24, Apr 19 '19 at 20:41

Martin · Answer 1 · 2019-04-19T20:52:42.457

0

You should set up PDO error logging.

From Comments; paraphrased for clarity:

My user_id column is int(11) auto_increment

Your problem is you are trying to insert a string value into a numerical column in MySQL.

user_id / id in database parlance is usually a numerical value, but you have not set the value type in your SQL, so it defaults to string.

Because your :id value is a numeric value in PHP you need to do this:

$stmt->bindParam(":id", $id, 'i');  // i = integer type.

It is highly recommended to explicitly set the value of the data type supplied each and every time .

If the data given to the PDO does not match the value-type given, then the PDO transaction will void and will not complete. This is a security measure.

For example:

$id = 3;
$stmt->bindParam(":id", $id);

This is the same as saying:

$stmt->bindParam(":id", 3, 's'); // default type value is 's' for string.

Obviously the value 3 is not a string so this transacion ($stmt) is never performed.

i assume it's because it views the description as a special word, if that is true then i should change the name in my database. Thoughts?

"description" is neither a Keyword or a reserved word in MySQL 5.5-->5.7
(in MySQL 8.0.4 DESCRIPTION is a keyword but is not a reserved word)
You can view a list of MySQL Keywords and Reserved words .

edited Apr 19 '19 at 20:52

answered Apr 19 '19 at 20:36

Martin

22,212
11
70
132

It is supposed to abandon the code if the invalid adress is given, thou if no adress is given the description should be updated, i will try this, but i have my doubts about description, if the code views it as a key word, does PDO void the query? – Coa 24 Apr 19 '19 at 20:38
@Coa24 My answer will solve your issue. You need to always specify the correct data type when adding variables to PDO (or MySQLi) transactions. Thank you. – Martin Apr 19 '19 at 20:44
My issue has been solved @Martin, thanks for the help, the post above gave me a few good pieces that were just what i needed. My apologies for making you go red a bit, i myself am a bit tired of all this work. Have a nice day :-) – Coa 24 Apr 19 '19 at 20:51
@Coa24 what was the solution to your issue? – Martin Apr 19 '19 at 20:53
1

The description part in my profile.php page was checking for an int 0 instead of a string 0, since when you have a string and try to set it to null, the default value is 0, not NULL. If it's 0 or NULL it writes the default 'You have no description'. Setting a string 0 fixed that. As for the email, in all honesty, i have no idea. Out of the blue it just started working. And i know about hard refreshes and cache deletion. @Martin – Coa 24 Apr 19 '19 at 20:58

score 0 · Answer 2 · answered Apr 20 '19 at 10:03

Some notes about the logic:

if(isset($_POST['emailChange']) || isset($_POST['descChange']))
{
    $desc = $_POST['descChange'];
    $email = $_POST['emailChange'];
    ...

First you check, if at lease one parameter exists, but then you access both. You can argue, that the form send always both, but never believe user input: Manipulating data is so easy!

Either change your if(...) to:

if( isset($_POST['emailChange']) && isset($_POST['descChange']) )

The following line is a shorter form with identical semantics:

if( isset( $_POST['emailChange'], $_POST['descChange'] ) )

The other ways is to change the 2 other lines, for example by:

    $desc  = isset($_POST['descChange'])  ? $_POST['descChange']  : '';
    $email = isset($_POST['emailChange']) ? $_POST['emailChange'] : '';

PHP Update query is being executed, but the credentials are not updated

2 Answers2