How to use prepared statement for updating table instead of unprepared form

Question

update1: I tried all of the suggestion (much appreciated btw) but the execution still isn't going through displaying "Updated 0 rows ‌". I have tried multiple ways of approaching this but nothing seems to work for me.

Here is the code I have at the moment:

    if(isset($_POST['Done'])) {

        $updateCD = "UPDATE users SET lgs=?, its=?, hbs=?, bios=?, pls=?, cts=? WHERE email=?";
        $stmt = $con->prepare($updateCD);

        $stmt->bind_param('sssssss', $_POST['lgs'], $_POST['its'], $_POST['hbs'], $_POST['bios'], $_POST['pls'], $_POST['cts'], $_GET['email']);
        $stmt->execute();

          if ($stmt->errno) {
     echo "FAILURE!!! " . $stmt->error;
      }
   else {
    echo "Updated {$stmt->affected_rows} rows";

      $stmt->close();

     }

         }

I am wanting to increase the security of my code by replacing my basic sqli queries with prepared statements.

This UPDATE statement concerns updating the user's profile information in the database. Problem is, despite the models I followed, the update execution isn't getting through thus changing nothing in the database.

PHP code:

    $email_login = mysqli_real_escape_string($con, $GET['email']);
    $lg = mysqli_real_escape_string($con, $_POST['lgs']);
     $it = mysqli_real_escape_string($con, $_POST['its']);
     $hb = mysqli_real_escape_string($con, $_POST['hbs']);
     $bio = mysqli_real_escape_string($con, $_POST['bios']);
     $pl = mysqli_real_escape_string($con, $_POST['pls']);
    $ct = mysqli_real_escape_string($con, $_POST['cts']);

    if(isset($_POST['Done'])) {
     $updateCD = "UPDATE users SET lgs=?, its=?, hbs=?, bios=?, pls=?, cts=? WHERE email=?";
     $stmt = $con->prepare($updateCD);

        $stmt->bind_param('sssssss', $lg, $it, $hb, $bio, $pl, $ct, $email_login);
      $stmt->execute();

      if ($stmt->errno) {
     echo "FAILURE!!! " . $stmt->error;
      }
   else {
    echo "Updated {$stmt->affected_rows} rows";

      $stmt->close();

     }

         }

Note after closure: I understand why it may seem as if this question is similar to the other one, but by no means is it an exact duplicate. The referred question concerned updating a table in general whereas this question magnifies on using prepared statements to execute an update. I already know how to update a table without using prepared statements so I would like to improve my code syntax with a more reliable frameset. Although both questions are related, they have different scopes.

What is the return value of the `execute()` method? If it is `false` the call failed. [See the docs](https://secure.php.net/manual/en/mysqli-stmt.execute.php). — halfer, Jul 19 '15 at 08:04
Or if it was successful, maybe no records matched the supplied `email`? — halfer, Jul 19 '15 at 08:07
Are you sure that email contains a value, and it's obtained via GET? Moreover, you don't have to use the mysql_real_escape_string business, that's handled by PDO. Just use the POST variables directly in your bind_param call. — stef77, Jul 19 '15 at 10:43
On an unrelated note, why are you using `mysqli_real_escape_string`? You mention you want to switch to prepared statements for security, and the whole concept of prepared statements being more "secure" is because it handles the escaping, which is considered "safer" because it leaves security to the DB, not the developer (who might forget to escape the input). — Christian, Jul 19 '15 at 10:43
You don't need to use `mysqli_real_escape_string` at all when you use prepared statements. — Bhavesh G, Jul 19 '15 at 10:44
@ChristianVarga Yeah, I don't know why. I am getting confused as to what is necessary and what is not. — , Jul 19 '15 at 16:50

score 1 · Answer 1 · edited May 23 '17 at 12:14

1

Your $email_login variable is empty; even if it's obtained via GET, you have to use $_GET, and not $GET.

As said in various comments, don't use mysqli_real_escape_string, since reliable escaping is done by MySQLi itself. Just pass in the $_POST variables directly as arguments for bind_param.

Some additional ideas:

Learn about prepared statements: http://php.net/manual/en/pdo.prepared-statements.php
Learn about the superglobals: http://www.w3schools.com/php/php_superglobals.asp
Consider using PDO instead of MySQLi: mysqli or PDO - what are the pros and cons?, http://www.quora.com/As-a-PHP-beginner-should-I-learn-MySQLI-or-PDO-first

edited May 23 '17 at 12:14

Community

1
1

answered Jul 19 '15 at 11:12

stef77

1,000
5
19

Hi, I did as you suggested but the problem still persists. I have updated the OP. – Jul 19 '15 at 16:56
You seriously have to put more work in understanding what prepared statements are and what the superglobals $_GET and so on are. You will run into all other kinds of weirdnesses otherwise in the future. It makes no sense at all to trial-and-errorwise use $_SESSION. True, the value may be stored there, but if you don't understand why, you're missing very important basics. That's not meant to discourage you. You will profit greatly from this understanding, with respect to every web related task you are confronted with in the future, by all means not only PHP related. Invest the time it takes. – stef77 Jul 19 '15 at 17:25

How to use prepared statement for updating table instead of unprepared form

1 Answers1