Insertion not happening on edit submit

shruti · December 9, 2019, 12:11pm

I have a tool where I am trying to insert the details in the database. Initially during the submission of the form for the first time, entire data is getting inserted into the database but when I try click on edit button and try adding the details, the data is not getting inserted.
The create.php contains the form wherein all the details needs to be filled in, on click of submit the data redirects to another page “generate.php” where the actual queries are written. Now when I click on the “Amend” buttonit redirects to “edit.php” file where again the form details are submitted and on submit it redirects to “generate.php”. But the data from edit.php file is not getting inserted.

create.php

<form name="MainBTA" id="MainBTA" onsubmit="return validateBTAOutsiderForm()" method="POST" action="bta_generate_outsider_request.php?bta_oid=<?php echo $bta_oid; ?>"  align="center">
                <table align="center" style="width:880px" border="0">.......</table>
<table width="968px" border="0" cellspacing="0" cellpadding="0" class="submitbg" align="center" >
    <tr class="submitbg">
    <td height="20px" colspan="4" ></td>
    </tr>
        <tr>
            <td width="416">&nbsp;</td>
              <td width="86"><input type="reset" name="bta_reset" value="Reset" style="width:60px;" /></td>
            <td width="116">
                <input type="submit" name="bta_out_submit" id="bta_out_submit" value="Submit"  style="width:60px;"/>
          </td>
            <td width="350">&nbsp;</td>
        </tr>
        <tr class="submitbg">
    <td height="20px" colspan="4" ></td>
    </tr>
</table>

</form>

astonecipher · December 9, 2019, 12:49pm

Where is the code that does the update query?

shruti · December 9, 2019, 12:51pm

generate.php

    function parseValues($inputArr) {
    $bta_outsider_conn = mysqli_connect('localhost', 'root', '', 'tools_bta_outsider');
    $inputarr_det = array();
    foreach ($inputArr as $key => $det) {
        $arraySize = sizeof($det);
        for ($i = 0; $i < $arraySize; $i++) {
            $value = trim($det[$i]);
            if (is_string($value))
                $value = mysqli_real_escape_string($bta_outsider_conn, $value);
            $inputarr_det[$i][$key] = $value;
        }
    }
    return $inputarr_det;
    }
        if (isset($_POST['edit_submit'])) {
        if (mysqli_query($bta_outsider_conn, "DELETE FROM bta_out_itenary where bta_oid = '".$bta_id."'")) {
            echo "Travel info deleted successfully <br />";
        }        
        if (mysqli_query($bta_outsider_conn, "DELETE FROM bta_out_accomodation where bta_oid = '".$bta_id."'")) {
            echo "Accomodation info deleted successfully <br />";
        }
        if (mysqli_query($bta_outsider_conn, "DELETE FROM bta_out_expences where bta_oid = '".$bta_id."'")) {
            echo "Travel exp info deleted successfully <br />";
        }
        if (mysqli_query($bta_outsider_conn, "DELETE FROM bta_form19_details where bta_id = '".$bta_id."'")) {
            echo "Form19 info deleted successfully <br />";
        }

    if (!empty($_POST['travel_det'])) {
        $travelDet = parseValues($_POST['travel_det']);
        foreach ($travelDet as $det) {
            if(isset($det['to_airport'])) {
                $toAirport = $det['to_airport'];
            }
            else {
                $toAirport=0;
            }

            if(isset($det['from_airport'])) {
                $fromAirport = $det['from_airport'];
            }
            else {
                $fromAirport =0;
            }
            $amt = (!empty($det['est_cost'])) ? $det['est_cost'] : 0;
            $dep_place = $arr_place = '';
            $dep_place = (!empty($det['dep_place_city'])) ? $det['dep_place_city'] : $det['dep_place']; //get entered city for international
            $arr_place = (!empty($det['arr_place_city'])) ? $det['arr_place_city'] : $det['arr_place']; //get entered city for international
            //insert query
            $query = "
INSERT INTO `tools_bta_outsider`.`bta_out_itenary` 
    (`bta_oid`
    ,`departure`
    ,`departure_country`
    ,`arrival`
    ,`arrival_country`
    ,`dept_date`
    ,`dept_time`
    ,`arrival_date`
    ,`arrival_time`
    ,`travel_mode`
    ,`amount`
    ,`currency`
    ,`to_airport`
    ,`from_airport`) 
VALUES (
    '".$bta_id."'
    ,'" . $dep_place . "'
    ,'" . $det['dep_place'] . "'
    ,'" . $arr_place . "'
    ,'" . $det['arr_place'] . "'
    ,'" . date('Y-m-d', strtotime($det['dep_date'])) . "'
    ,'" . $det['time_zone1'] . "'
    ,'" . date('Y-m-d', strtotime($det['arr_date'])) . "'
    ,'" . $det['time_zone2'] . "'
    ,'" . $det['t_mode'] . "'
    ,'" . $amt . "'
    ,'" . $_POST['travel_det_currency'] . "'
    , '".$toAirport."'
    , '".$fromAirport."')";
   
            $res = mysqli_query($bta_outsider_conn, $query);
                if (!$res) {
                    die('bta_travel_itenary table insert failed' . mysqli_error($bta_outsider_conn));  
                    trace_log(__FILE__, __LINE__, $bta_id . " : " . $query . mysqli_error($bta_outsider_conn));                 
                }
                else{
                    trace_log(__FILE__, __LINE__, $bta_id . " : History Updated Sucessfully.");
                }
        }
    }

shruti · December 9, 2019, 12:53pm

Sorry for submitting half the code. The edit.php file contains same as create.php only with the difference of form name and id and submit button name.

edit.php

<form method="POST" name="EditBTA" onsubmit="return validateBTAEditForm()" action="bta_generate_outsider_request.php?bta_oid=<?php echo $bta_id ?>" align="center">
                     <!-- <form method="POST" name="EditBTA" onsubmit="return validateBTAEditForm()" action="test.php?bta_oid=<?php echo $bta_id ?>" align="center"> -->
                        <div id="formbody" align="center"> </div>
                        <div class="formframe"> </div>
                        <table width="850" border="0" cellspacing="0" cellpadding="0" align="center">
                            <tr>
                                <td width="850" class="tableheader" align="left">Request Id : <?php echo $bta_id; ?></td>
                            </tr>
                            <tr>
                                <td width="850" align="center">&nbsp; </td>
                            </tr>
                            <tr>
                                <td width="850" class="tableheader" align="left">General</td>
                            </tr>
                            <tr>
                                <td align="left">
                                    <table width="850" border="0" cellspacing="0" cellpadding="0" align="center">
                                        <tr>
                                            <td colspan="6">&nbsp;</td>
                                        </tr>
                                        <tr>
                                            <td width="110" align="left">TGI No</td>
                                            <td width="180" class="bluetext"><div align="left">
                                                    <?php echo $emp_det['tgi']; ?>
                                                </div></td>
                                            <td width="110">First Name</td>
                                            <td width="180" class="bluetext"><div align="left">
                                                    <?php echo $emp_det['first_name'], ' ',$emp_det['last_name']; ?>
                                                </div></td>...
<tr>
                                            <td class="submitbg" align="center">
                                                <table width="850" border="0" cellspacing="0" cellpadding="0">
                                                    <tr>
                                                        <td width="320">&nbsp;</td>
        												<!-- <td><input type="submit" name="Submit" value="Save" style="width:60px;"/></td> -->
                                                       <!-- <td> <input type="submit" name="save_edited" value="Save" style="width:60px;"/>
                                                             <input type="button" name="edit_print" value="Print" style="width:60px;" onClick="javascript:window.print()"/>
                                                         </td> -->
                                                        <td>
                                                        	<input type="submit" name="edit_submit" id="edit_submit" value="Submit" style="width:60px;"/>
                                                            <!-- <input type="submit" name="bta_out_submit" id="bta_out_submit" value="Submit"  style="width:60px;"/> -->
                                                        </td>
                                                        <td width="320">&nbsp;</td>
                                                    </tr>
                                                </table>
                                            </td>
                                        </tr>

astonecipher · December 9, 2019, 1:01pm

I dont see any update statements to handle the edited portion?

shruti · December 9, 2019, 1:05pm

In this as you can see, on submit of edit_submit all the details are deleted from tables and just below that new values are inserted. This is the portion where I am unable to understand the workflow. If i write update query there is multiple values getting inserted such as if 2 rows are added and now if I add 1 row then total 3 rows must be added but the output that I am getting is 2+2+1.

astonecipher · December 9, 2019, 1:07pm

Why would you delete the record and re-insert if a record exists?

shruti · December 9, 2019, 1:08pm

I have no idea as to why this requirement exists but it has to be followed in the same way. Need to delete and insert new record along with prev records.

astonecipher · December 9, 2019, 1:16pm

So, you can’t make the code better?

For instance, you really should be using prepared statements rather than concatenating the query together like it is now.

shruti · December 9, 2019, 1:25pm

No I cannot change much in it as it is running on live server. I am facing issues wrt amendment of data. Any suggestions that I can use in the above query. I tried using everything but no luck so far.

astonecipher · December 9, 2019, 1:26pm

You are exposing all kinds of bad practices now.

Make a local copy. Get it working locally, and fixed properly, then deploy it to prod.

shruti · December 9, 2019, 1:28pm

Yes I am working locally as of now, in local I tried to dump the query using var_dump($_POST). The data that I am entering isn’t passing the function “parsevalue()”

astonecipher · December 9, 2019, 1:29pm

Take a look at what you are doing as well:

shruti:

        if (mysqli_query($bta_outsider_conn, "DELETE FROM bta_out_itenary where bta_oid = '".$bta_id."'")) {
            echo "Travel info deleted successfully <br />";
        }        
        if (mysqli_query($bta_outsider_conn, "DELETE FROM bta_out_accomodation where bta_oid = '".$bta_id."'")) {
            echo "Accomodation info deleted successfully <br />";
        }
        if (mysqli_query($bta_outsider_conn, "DELETE FROM bta_out_expences where bta_oid = '".$bta_id."'")) {
            echo "Travel exp info deleted successfully <br />";
        }
        if (mysqli_query($bta_outsider_conn, "DELETE FROM bta_form19_details where bta_id = '".$bta_id."'")) {
            echo "Form19 info deleted successfully <br />";
        }

So, you have deleted data from 4 tables…

shruti:

           $query = "
INSERT INTO `tools_bta_outsider`.`bta_out_itenary` 
    (`bta_oid`
    ,`departure`
    ,`departure_country`
    ,`arrival`
    ,`arrival_country`
    ,`dept_date`
    ,`dept_time`
    ,`arrival_date`
    ,`arrival_time`
    ,`travel_mode`
    ,`amount`
    ,`currency`
    ,`to_airport`
    ,`from_airport`) 
VALUES (
    '".$bta_id."'
    ,'" . $dep_place . "'
    ,'" . $det['dep_place'] . "'
    ,'" . $arr_place . "'
    ,'" . $det['arr_place'] . "'
    ,'" . date('Y-m-d', strtotime($det['dep_date'])) . "'
    ,'" . $det['time_zone1'] . "'
    ,'" . date('Y-m-d', strtotime($det['arr_date'])) . "'
    ,'" . $det['time_zone2'] . "'
    ,'" . $det['t_mode'] . "'
    ,'" . $amt . "'
    ,'" . $_POST['travel_det_currency'] . "'
    , '".$toAirport."'
    , '".$fromAirport."')";
   
            $res = mysqli_query($bta_outsider_conn, $query);

And to replace that, you are inserting data into only one of those tables? Does that make sense to you?

astonecipher · December 9, 2019, 1:32pm

It’s expecting an array and you are passing in a string.

    function parseValues($inputArr) {
    $bta_outsider_conn = mysqli_connect('localhost', 'root', '', 'tools_bta_outsider');
    $inputarr_det = array();
    foreach ($inputArr as $key => $det) {
        $arraySize = sizeof($det);
        for ($i = 0; $i < $arraySize; $i++) {
            $value = trim($det[$i]);
            if (is_string($value))
                $value = mysqli_real_escape_string($bta_outsider_conn, $value);
            $inputarr_det[$i][$key] = $value;
        }
    }

if (!empty($_POST['travel_det'])) { 
    $travelDet = parseValues(array($_POST['travel_det'])); 
// would have used array shorthand, but this makes it clear that it is using an array

benanamen · December 9, 2019, 4:55pm

You should find out. The whole approach is wrong.

ErnieAlex · December 9, 2019, 5:09pm

IMHO, it makes no sense at all! Delete records and then put them back out? Unless there is some sort of trigger system running to back up transactions on the insert functions. Seems like a total waste of server power and database usage…

astonecipher · December 9, 2019, 5:21pm

On this…

I fully agree with benanamen’s statement. I am a big proponent of leaving code better than you found it. If that means refactoring large chunks, note it now, alert others regarding the technical debt and determine if it can be corrected.

This code shows security holes, which should be a major issue for most orginizations.
It is poorly executed, so performance is effected.

For instance, the helper method is redundant and unneeded.
The logging and error handling is also poorly executed. You shouldn’t die on a bad query, it should be handled gracefully, with relevant information to the user, not an actual error message. The printing of “deleted successfully” is a business aspect, one that displays too much information on the inner workings of the system and should not be known to a user. You have a trace file but no audit files if something was done out of maliciousness, because I don’t see anything that handles things from a secure prospective. Anyone could send a delete message in and there is nothing to prevent that from happening or logging that it happened other than the trace file saying it was updated.

shruti · December 27, 2019, 8:17am

Thank you for the advice. I really appreciate it and yes I will discuss and mke the necessary improvements. Thank you @benanamen, @ErnieAlex