0

For starters this is an internal website for my company, running sql serv. This is my first time ever doing anything with AJAX. I am literally a month into PHP, SQL, Javascript.. haven't messed with jQuery in ages and what I know is from codeacademy.. lol.

Below I have the jQuery script, HTML, and the SQL. The issue is residing around the EDIT / UPDATE portion of the jQuery. I put the HTML and SQL for reference.

The deal is.. if I click Edit on User1, it'll store the EeNumberID (1) as well as all his data from hidden inputs (any recommendations on better method?), then I click User2 it'll store his EeNumberID (2). Then when I update User2 - it'll also update User1 with his information - even though his EeNumberID is different.. it's like it's passing two lines of updateData to the management-queries.php and calling for two lines in the database to be updated.. but why?

Is it creating EeNumberID into an array once I select a second user to edit? Does it store this variable multiple times locally? I tried various ways to display the variable and it is displaying as how it should be sent to the SQL update.

Help! :(

JQUERY

$(document).ready(function(){
//LETS HIDE WHAT WE DON'T INITIALLY NEED TO SEE
$("#updateuser, #canceledit").hide();
//BY DEFAULT WHEN THE PAGE LOADS SHOW ALL USERS
//WE ALSO PASS LOCATION AND PERMISSION SO BY DEFAULT USER CAN ONLY SEE WHAT THEY'RE ALLOWED TO
function showUsers(){
    $.ajax({
        type: "post",
        url: "admin/management-queries.php",
        data: "action=showusers&location=<?php echo $location?>&permission=<?php echo $permission?>",
        success:function(data){
            $("#userlist").html(data);
            //EVERY TIME A LIST OF USERS IS GENERATED WE NEED TO HIDE CANCEL EDIT BUTTONS
            $(".cancel-button").hide();
        }
    });
}
showUsers();

//ADD USER
$("#adduser").click(function(){
    //EVERYTIME 'ADD USER' IS CLICKED WE NEED TO CLEAR ANY PREVIOUS ERROR MESSAGES
    $("#UserFullName,#UserPassword,#UserPassword2,#UserEmail,#UserLocation,#UserPermission").removeClass("form-error");
    $("span.label").remove();

    var fullname = $("#UserFullName").val();
    var password = $("#UserPassword").val();
    var password2 = $("#UserPassword2").val();
    var email = $("#UserEmail").val();
    var location = $("#UserLocation").val();
    var permission = $("#UserPermission").val();

    //BEFORE WE PASS THE DATA WE NEED TO CHECK IF IT'S ALL THERE
    if (fullname == "" || password != password2 || email == "" || location == null || permission == null) {
        if (fullname == "") {
            $("#UserFullName").addClass("form-error").after("<span class='label label-danger'>This field is required.</span>");
        };
        if (password != password2) {
            $("#UserPassword").addClass("form-error");
            $("#UserPassword2").addClass("form-error").after("<span class='label label-danger'>Passwords do not match.</span>");
        } else if (password == "" || password2 == "") {
            $("#UserPassword").addClass("form-error");
            $("#UserPassword2").addClass("form-error").after("<span class='label label-danger'>These fields are required.</span>");
        };
        if (email == "") {
            $("#UserEmail").addClass("form-error").after("<span class='label label-danger'>This field is required.</span>");
        };
        if (location == null) {
            $("#UserLocation").addClass("form-error").after("<span class='label label-danger'>This field is required.</span>");
        };
        if (permission == null) {
            $("#UserPermission").addClass("form-error").after("<span class='label label-danger'>This field is required.</span>");
        };

    } else {
        var addData = "UserEmail="+email+"&UserPassword="+password+"&UserLocation="+location+"&UserFullName="+fullname+"&UserPermission="+permission+"&action=adduser";
        $.ajax({
            type: "post",
            url: "admin/management-queries.php",
            data: addData,
            success:function(data){
                //IF THE DATA IS ADDED WE NEED TO CLEAR THE INPUT FIELDS
                $("#UserFullName, #UserPassword, #UserPassword2, #UserEmail, #UserLocation, #UserPermission").val('');
                showUsers();
            }
        });
    };
});

//DELETE USER
$("body").on("click", "#userlist .delete-button", function(e) {
    e.preventDefault();
    var clickedID = this.id.split('-');
    var DbNumberID = clickedID[1];
    var delData = 'deleteuser='+ DbNumberID;

    jQuery.ajax({
        type: "post",
        url: "admin/management-queries.php",
        data: delData,
        success:function(data){
            showUsers();
        }
    });
});

//EDIT USER
$("body").on("click", "#userlist .edit-button", function(e) {
    $(".edit-button").show();

    var EeNumberID = $(this).attr('name');

    //GET ALL THE VARIABLES VIA HIDDEN INPUTS POPULATED BY SQL
    var EditName = $("#Name-"+EeNumberID).val();
    var EditPassword = $("#Password-"+EeNumberID).val();
    var EditEmail = $("#Email-"+EeNumberID).val();
    var EditLocation = $("#Location-"+EeNumberID).val();
    var EditPermission = $("#Permission-"+EeNumberID).val();

    //LETS ADJUST VISUALS AS WE ARE IN EDIT MODE
    $("#adduser, .cancel-button, #edit-"+ EeNumberID).hide();
    $("#canceledit, #updateuser, #cancel-"+ EeNumberID).show();
    $(".row").removeClass("sel-edit");
    $("#user-"+ EeNumberID).addClass("sel-edit");

    //PASS VARIABLE VALUES TO INPUT FIELDS
    $("#UserFullName").val(EditName);
    $("#UserPassword, #UserPassword2").val(EditPassword);
    $("#UserEmail").val(EditEmail);
    $("#UserLocation").val(EditLocation);
    $("#UserPermission").val(EditPermission);

    //CANCEL EDIT (FROM WITHIN ROW)
    $("#cancel-"+ EeNumberID).click(function(){
        //ADJUST VISUALS FOR CANCEL
        $("#canceledit, #updateuser, .cancel-button").hide();
        $("#adduser, .edit-button").show();
        $(".row").removeClass("sel-edit");

        //CLEAR INPUT FIELDS
        $("#UserFullName, #UserPassword, #UserPassword2, #UserEmail, #UserLocation, #UserPermission").val('');
    });
    //UPDATE USER (THIS MUST BE NESTED WITHIN 'EDIT USER' SO WE KNOW THE ROW CURRENTLY OPENED)
    $("#updateuser").click(function(){
        //EVERYTIME 'UPDATE USER' IS CLICKED WE NEED TO CLEAR ANY PREVIOUS ERROR MESSAGES
        $("#UserFullName,#UserPassword,#UserPassword2,#UserEmail,#UserLocation,#UserPermission").removeClass("form-error");
        $("span.label").remove();

        var ID = EeNumberID;
        var fullname = $("#UserFullName").val();
        var password = $("#UserPassword").val();
        var password2 = $("#UserPassword2").val();
        var email = $("#UserEmail").val();
        var location = $("#UserLocation").val();
        var permission = $("#UserPermission").val();

        //BEFORE WE PASS THE DATA WE NEED TO CHECK IF IT'S ALL THERE
        if (fullname == "" || password != password2 || email == "" || location == null || permission == null) {
            if (fullname == "") {
                $("#UserFullName").addClass("form-error").after("<span class='label label-danger'>This field is required.</span>");
            };
            if (password != password2) {
                $("#UserPassword").addClass("form-error");
                $("#UserPassword2").addClass("form-error").after("<span class='label label-danger'>Passwords do not match.</span>");
            } else if (password == "" || password2 == "") {
                $("#UserPassword").addClass("form-error");
                $("#UserPassword2").addClass("form-error").after("<span class='label label-danger'>These fields are required.</span>");
            };
            if (email == "") {
                $("#UserEmail").addClass("form-error").after("<span class='label label-danger'>This field is required.</span>");
            };
            if (location == null) {
                $("#UserLocation").addClass("form-error").after("<span class='label label-danger'>This field is required.</span>");
            };
            if (permission == null) {
                $("#UserPermission").addClass("form-error").after("<span class='label label-danger'>This field is required.</span>");
            };

        } else {
            var updateData = "ID="+ID+"&UserEmail="+email+"&UserPassword="+password+"&UserLocation="+location+"&UserFullName="+fullname+"&UserPermission="+permission+"&action=updateuser";

            $.ajax({
                type: "post",
                url: "admin/management-queries.php",
                data: updateData,
                success: function(data){
                    $("#UserFullName, #UserPassword, #UserPassword2, #UserEmail, #UserLocation, #UserPermission").val('');
                    $("#canceledit, #updateuser, .cancel-button").hide();
                    $("#adduser, .edit-button").show();
                    showUsers();
                }
            });
        };
    });
}); 

//CANCEL EDIT (GLOBAL)
$("#canceledit").click(function(){
    //ADJUST VISUALS FOR CANCEL
    $("#canceledit, #updateuser, .cancel-button").hide();
    $("#adduser, .edit-button").show();
    $(".row").removeClass("sel-edit");

    //CLEAR INPUT FIELDS
    $("#UserFullName, #UserPassword, #UserPassword2, #UserEmail,#UserLocation, #UserPermission").val('');
});
});

HTML INCLUDE(BOOTSTRAP DRIVEN)

<div class="container-fluid">
    <div class="row">
        <div class="col-xs-4 panel-container">
            <div class="panel panel-default">
                <div class="panel-heading">&nbsp;</div>
                <div class="panel-body">
                    <form>
                        <div class="row input-padding">
                            <div class="col-xs-12"><input class="form-control" type="text" name="UserFullName" id="UserFullName" placeholder="Full Name"></input></div>
                        </div>
                        <div class="row input-padding">
                            <div class="col-xs-12"><input class="form-control" type="password" name="UserPassword" id="UserPassword" placeholder="Password"></input></div>
                        </div>
                        <div class="row input-padding">
                            <div class="col-xs-12"><input class="form-control" type="password" name="UserPassword2" id="UserPassword2" placeholder="Confirm Password"></input></div>
                        </div>
                        <div class="row input-padding">
                            <div class="col-xs-12"><input class="form-control" type="text" name="UserEmail" id="UserEmail" placeholder="E-Mail"></input></div>
                        </div>
                        <div class="row input-padding">
                            <div class="col-xs-12">
                                <select name="UserLocation" id="UserLocation" class="form-control">
                                    <?php
                                    $sqlresult = sqlsrv_query($connection, "SELECT * FROM MESCO_locations");
                                    echo '<option value="" disabled'; if(!isset($VALLocation)) {echo' selected';} echo ' hidden>Select User Location</option>';
                                    while($row = sqlsrv_fetch_array($sqlresult)) {
                                    echo '<option value="'.$row['location_id'].'">'.$row['location_name'].'</option>';
                                    }
                                    ?>
                                </select>
                            </div>
                        </div>
                        <div class="row input-padding">
                            <div class="col-xs-12">
                                <select name="UserPermission" id="UserPermission" class="form-control">
                                    <option value="" disabled selected hidden>Select User Permission</option>
                                    <option value="0">Disabled</option>
                                    <?php
                                    if ($_SESSION['Permission'] == 1) {
                                    echo '  <option value="1">Administrator</option>';
                                    }
                                    if ($_SESSION['Permission'] <= 2) {
                                    echo '  <option value="2">Branch Manager</option>';
                                    }
                                    if ($_SESSION['Permission'] <= 3) {
                                    echo '  <option value="3">Warehouse Manager</option>
                                            <option value="4">Warehouse Employee</option>
                                        ';
                                    }
                                    if ($_SESSION['Permission'] <= 2) {
                                    echo '  <option value="5">Purchasing</option>
                                            <option value="6">Inside Sales</option>
                                            <option value="7">Outside Sales</option>
                                            <option value="8">Accounts Receivable</option>';
                                    }
                                    ?>
                                </select>
                            </div>
                        </div>
                        <div class="row input-padding input-button">
                            <div class="col-xs-12">
                                <button type="button" class="btn btn-sm btn-info" id="updateuser">UPDATE USER</button>
                                <button type="button" class="btn btn-sm btn-warning" id="canceledit">CANCEL</button>
                                <button type="button" class="btn btn-sm btn-success" id="adduser">ADD USER</button>
                            </div>
                        </div>
                    </form>
                </div>
            </div>
        </div>
        <div class="col-xs-8 panel-container">
            <div class="panel panel-default">
                <div class="panel-heading">
                    <strong><i class="fa fa-users"></i>&nbsp;&nbsp;User Management</strong>
                </div>
                <div class="panel-body">                    
                    <div class="row row-heading error-data">
                        <div class="col-xs-2">Full Name</div>
                        <div class="col-xs-4 center">E-Mail</div>
                        <div class="col-xs-1 center">Location</div>
                        <div class="col-xs-3 center">Permission</div>
                        <div class="col-xs-2 center">EDIT / DEL</div>
                    </div>
                    <hr class="margin-reset" />
                    <div class="row row-data error-data">
                        <div class="col-xs-12" id="userlist"></div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

PHP/SQL

////----------------------- INITIATE -----------------------////

set_time_limit(0); //SETS TIME LIMIT FOR SCRIPT EXECUTION - 0 = UNLIMITED
require_once('C:\inetpub\wwwroot\connection.php'); //CUSTOM ROOT FOR AJAX QUERIED CONNECTIONS
$connectionInfo = array("UID" => $myUser, "PWD" => $myPass, "Database" => $myDB, "ReturnDatesAsStrings" => true);
$connection = sqlsrv_connect($myServer, $connectionInfo);
//IF CONNECTION IS NOT ESTABLISHED THROW MESSAGE AND END SCRIPT
if( !$connection ) {
    die("Connection to MS SQL could not be established.\n");
}
//AJAX POST ON WHAT QUERY ACTION TO TAKE (Example: ?action=showusers)
$action = $_POST["action"];

//CURRENT USER PERMISSION AND LOCATION
$permission = $_POST["permission"];
$location = $_POST["location"];


////----------------------- USER MANAGEMENT -----------------------////


//USER MANAGEMENT - DISPLAY FOR WHAT THEY USER ALWAYS SEES
if($action == "showusers") {
    //WE NEED TO ASSESS WHAT THE USER HAS THE ABILITY TO SEE
    if ($permission == 1) {
        //BY DEFAULT ADMINISTRATORS CAN SEE ALL USERS FOR ALL BRANCHES
        $sqlresult = sqlsrv_query($connection, "SELECT * FROM MESCO_logins ORDER BY Location ASC, Name ASC");
    } else if ($permission == 3) {
        //BY DEFAULT WAREHOUSE MANAGERS CAN ONLY SEE DISABLED USERS, WAREHOUSE MANAGERS, AND WAREHOUSE EMPLOYEES FOR THEIR BRANCH
        $sqlresult = sqlsrv_query($connection, "SELECT * FROM MESCO_logins WHERE Location = '$location' AND Permission_Level in (3,4,0) ORDER BY Location ASC, Name ASC");
    } else {
        //ALL OTHER USERS CAN SEE ALL USERS FOR THEIR BRANCH (TEMPORARY)
        $sqlresult = sqlsrv_query($connection, "SELECT * FROM MESCO_logins WHERE Location = '$location' ORDER BY Location ASC, Name ASC");
    }
    while($row = sqlsrv_fetch_array($sqlresult)) {
        echo '
        <div class="row row-data" id="user-'.$row[ID].'">
            <div class="col-xs-2"><input type="hidden" id="Name-'.$row[ID].'" value="'.$row[Name].'">'.$row['Name'].'</div>
            <div class="col-xs-4 center"><input type="hidden" id="Email-'.$row[ID].'" value="'.$row[Email].'">'.$row['Email'].'</div>
            <div class="col-xs-1 center"><input type="hidden" id="Location-'.$row[ID].'" value="'.$row[Location].'">'.$row['Location'].'</div>
            <div class="col-xs-3 center"><input type="hidden" id="Permission-'.$row[ID].'" value="'.$row[Permission_Level].'">';
                //LETS CONVERT PERMISSION NUMERICS TO TEXT
                //THIS IS TEMPORARY - IDEALLY WE CAN HAVE A JOINED TABLE OR STORE THE TEXT DIRECTLY IN THE USER LOGIN TABLE FOR LESS CODE
                if ($row['Permission'] == '0') {
                    echo 'Disabled / Inactive';
                } else if ($row['Permission_Level'] == '1') {
                    echo 'Administrator';
                } else if ($row['Permission_Level'] == '2') {
                    echo 'Branch Manager';
                } else if ($row['Permission_Level'] == '3') {
                    echo 'Warehouse Manager';
                } else if ($row['Permission_Level'] == '4') {
                    echo 'Warehouse Employee';
                } else if ($row['Permission_Level'] == '5') {
                    echo 'Purchasing';
                } else if ($row['Permission_Level'] == '6') {
                    echo 'Inside Sales';
                } else if ($row['Permission_Level'] == '7') {
                    echo 'Outside Sales';
                } else if ($row['Permission_Level'] == '8') {
                    echo 'Accounts Receivable';
                }
            echo'
            </div>
            <div class="col-xs-2 center action-buttons">
                <input type="hidden" id="Password-'.$row[ID].'" value="'.$row[Password].'">
                <button type="button" class="btn btn-xs btn-warning cancel-button" name="Cancel Edit" id="cancel-'.$row['ID'].'"><i class="fa fa-ban"></i></button>
                <button type="button" class="btn btn-xs btn-info edit-button" name="'.$row['ID'].'" id="edit-'.$row['ID'].'"><i class="fa fa-pencil"></i></button>
                <button type="button" class="btn btn-xs btn-danger delete-button" name="Delete User" id="delete-'.$row['ID'].'"><i class="fa fa-times"></i></button>
            </div>  
        </div>';
    }
}
//USER MANAGEMENT - ADD USER
if($action == "adduser") {
    $ADDEmail = $_POST['UserEmail'];
    $ADDPassword = $_POST['UserPassword'];
    $ADDLocation = $_POST['UserLocation'];
    $ADDFullName = $_POST['UserFullName'];
    $ADDPermission = $_POST['UserPermission'];

    $sqlquery = "INSERT INTO MESCO_logins (Email, Password, Location, Name, Permission_Level)  VALUES ('$ADDEmail', '$ADDPassword', '$ADDLocation', '$ADDFullName', '$ADDPermission')";
    $sqlresult = sqlsrv_query($connection, $sqlquery);
}
//USER MANAGEMENT - DELETE USER
if (isset($_POST["deleteuser"])) {
    $DELID = $_POST["deleteuser"];

    $sqlquery = "DELETE FROM MESCO_logins WHERE ID = '$DELID'";
    $sqlresult = sqlsrv_query($connection, $sqlquery);
}
//USER MANAGEMENT - UPDATE USER
if($action == "updateuser") {
    $ID = $_POST["ID"];
    $UPDATEEmail = $_POST['UserEmail'];
    $UPDATEPassword = $_POST['UserPassword'];
    $UPDATELocation = $_POST['UserLocation'];
    $UPDATEFullName = $_POST['UserFullName'];
    $UPDATEPermission = $_POST['UserPermission'];

    $sqlquery = "UPDATE MESCO_logins SET Email = '$UPDATEEmail', Password = '$UPDATEPassword', Location = '$UPDATELocation', Name = '$UPDATEFullName', Permission_Level = '$UPDATEPermission' WHERE ID = '$ID'";
    $sqlresult = sqlsrv_query($connection, $sqlquery);
}
?>
Cody J.
  • 1
  • 2
  • 1
    [Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php). Using PDO [is really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Apr 08 '16 at 21:03
  • Please use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). – Jay Blanchard Apr 08 '16 at 21:04
  • This site is hosted internally and isn't available to the public. Thus the lack of security. Actually I haven't put one ounce of focus on security as I know it will only be internal. – Cody J. Apr 08 '16 at 22:00

2 Answers2

0

In your PHP/SQL file, you have a systemic problem with quoting array elements, for instance:

   while($row = sqlsrv_fetch_array($sqlresult)) {
        echo '
        <div class="row row-data" id="user-'.$row[ID].'">
            <div class="col-xs-2"><input type="hidden" id="Name-'.$row[ID].'" value="'.$row[Name].'">'.$row['Name'].'</div>

Each and every $row[ID] needs to be corrected to $row['ID']. I'd also recommend turning on error reporting, if it's not already on. There is probably a series of cascading errors compromising the entire script.

Also, I would highly recommend taking a look at jQuery's .data, which allows easy storage of data on DOM elements.

larsAnders
  • 3,813
  • 1
  • 15
  • 19
  • Woops! It was still functioning normally haha. I do have error log enabled and it was spraying the log with those. Thanks for catching that. I'll look into what jQuery .data can do for me. :) – Cody J. Apr 08 '16 at 22:05
0

Ohhhh boy! I fixed it! =]

Not sure why it was acting the way it did.. But it was because I nested the 'update' within the 'edit'. To fix the OP I did the following:

Declared a global variable:

var EditID = {};

I then altered the initial edit script from:

var EeNumberID = $(this).attr('name');

to

EeNumberID = $(this).attr('name');
EditID.EeNumberID = EeNumberID;

Now that I have the ID local for grabbing edit information, I then stored it globally so update knows exactly what to target. I think for some reason because it was nested and the page wasnt necessarily 'refreshing' it remember what it had been told to 'edit' previously.. and would pass it ALL to the update function.

Update was un-nested from the EDIT and I altered the following line:

var ID = EeNumberID;

to

var ID = EditID.EeNumberID;

WORKS! :) If anyone else has input on how to write cleaner php/javascript/jquery/sql please let me know! I am still learning and ALL input is appreciated!

Cody J.
  • 1
  • 2