Modify/update database via PHP page help

[everythingisfree]

Hi,

 

I need some assistance updating a MySQL database from a PHP page to update a users username.

 

I'm using SESSION's to store data (as such: the user ID), and concatenating it into the string for a global use.

 

The data is fetched and displayed into the field as expected:

<?
if(!$_POST['submit']){
$query = "SELECT * FROM users WHERE id = '" . $_SESSION['id'] . "'";
connectDB(); 
$result = mysqli_query($_SESSION['db'],$query);
closeDB();
$row = mysqli_fetch_array($result);
?>

The form looks like:

<form method="post" action="<?=$_SERVER['php_self']?>">
<p><label for="username">Username </label><input type="text" name="username" value="<?=$row['username']?>"></p>
<button type="submit" name="submit" value="update">Update</button>
</form>

and the query update string:

$query = "UPDATE username WHERE id = '" . $_SESSION['id']; "' SET $username ='" . $username . "'";

followed by an echo that let's the use know it's been successful (probably not the best notification method nut anyway)...

 

With the above code, it will not update, I can edit the name, press update and it passes through, says that it was successful but it did not actually send the data onto the database server. I researched this however everyone's answer does not solve mine, am I not doing something correct?.

 

Any help is much appropriated!

[benanamen]

You have the order of the query wrong. UPDATE SET WHERE

UPDATE table_name

SET column1=value, column2=value2,...

WHERE some_column=some_value 

By the way, $_SERVER['php_self'] is vulnerable to an XSS Attack.

 

You need to use prepared statements as well. You NEVER EVER send user supplied data directly to the database.

 

Don't SELECT *. Specify the column names you want.

 

You don't have to manually close the DB connection. Php does it automatically when the script is done running.

 

This doesn't make sense. If NOT submitted? Your logic is flawed.

if(!$_POST['submit']){

Edited December 5, 2016 by benanamen

[Jacques1]

The whole code is strange, to say the least. For example, why would you try to store the database connection in the session? It's not possible, and it doesn't make any sense.

 

You also need to learn how to use mysqli correctly. Or better yet, forget about mysqli and learn PDO instead. It's much more programmer-friendly.

 

Then you absolutely must learn the basics of security. This tiny piece of code is already full of vulnerabilities, and if you plan to put it online, you put your server and all of its data at a huge risk. Learn how to prevent cross-site scripting, SQL injections and cross-site request forgery. That's the minimum every programmer needs to know.

 

The function for connecting to the database, implemented with PDO:

<?php

const DB_HOST = 'localhost';
const DB_USER = '...';
const DB_PASSWORD = '...';
const DB_NAME = '...';
const DB_CHARSET = 'UTF8';



function connectToDB()
{
    $dsn = 'mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset='.DB_CHARSET;

    return new PDO($dsn, DB_USER, DB_PASSWORD, [
        PDO::ATTR_EMULATE_PREPARES => false,                // use actual prepared statements, don't emulate them
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,        // make PDO throw an exception in case of an error
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,   // fetch associative arrays by default
    ]);
}

Helper function for HTML-escaping to prevent XSS attacks:

<?php

const HTML_ENCODING = 'UTF-8';



function htmlEscape($rawInput)
{
    return htmlspecialchars($rawInput, ENT_QUOTES | ENT_SUBSTITUTE, HTML_ENCODING);
}

The main script:

<?php

session_start();

$databaseConnection = connectToDB();

// check if the user is logged in
if (!isset($_SESSION['id']))
{
    die('You must be logged in to view this page.');
}

// Did we receive a POST request?
if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
    // check if all required parameters are present
    if (!isset($_POST['username']))
    {
        die('Missing parameter: username');
    }

    // validate the username
    if (trim($_POST['username']) === '')
    {
        die('Username must not be blank or consist only of whitespace.');
    }

    // create a prepared statement to safely pass the username to the database system
    $usernameUpdateStmt = $databaseConnection->prepare('
        UPDATE
            users
        SET
            username = :username
        WHERE
            id = :id
    ');
    $usernameUpdateStmt->execute([
        'username' => $_POST['username'],
        'id' => $_SESSION['id'],
    ]);

    // the query will automatically throw an exception in case of an error, so if we've reached this point, everything is OK
    die('Username updated!');
}

$userStmt = $databaseConnection->prepare('
    SELECT
        username
    FROM
        users
    WHERE
        id = :id
');
$userStmt->execute([
    'id' => $_SESSION['id']
]);
$user = $userStmt->fetch();

?>
<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <title>Title</title>
    </head>
    <body>
        <!-- If you want to post the form to the same page, leave out the action attribute entirely; do not use $_SERVER['PHP_SELF'] -->
        <form method="post">
            <!-- The for attribute of labels must point to an ID, not a name; alternatively, just wrap the input in a label element -->
            <label >Username <input type="text" name="username" value="<?= htmlEscape($user['username']) ?>"></label>
            <button type="submit">Update</button>
        </form>
    </body>
</html>