Php / Jquery Forms & Datatables

Question

Hopefully someone can help. I have a customers database table which I am trying to display on a page using jquery datatables. That page will also contain a form to which I can input data and this will update the database and display on the table below. I have 3 files which I am using. Here is the code:

index.php

    <html> 
    <head> 
    <link rel="stylesheet" type="text/css" href="style.css">
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.6/jquery.js"></script>
    <script type="text/javascript" language="javascript" src="jquery.dataTables.js"></script>
    <script type="text/javascript" charset="utf-8">
            /* Table initialisation */
            $(document).ready(function() {
                $('#datatable').dataTable( {
                    "sDom": "<'row'<'span8'l><'span8'f>r>t<'row'<'span8'i><'span8'p>>"
                });
            });
    </script>
    <script type="text/javascript" language="javascript" src="jquery.form.js"></script>
    <script type="text/javascript"> 
        // prepare the form when the DOM is ready 
            $(document).ready(function() { 
            // bind form using ajaxForm 
            $('#htmlForm').ajaxForm({ 
                // target identifies the element(s) to update with the server response 
                target: '#datatable', 
                resetForm: 'true',

                // success identifies the function to invoke when the server response 
                // has been received; here we apply a fade-in effect to the new content 
                success: function() { 
                    $('#datatable').fadeIn('slow'); 
                } 
            });
        }); 
    </script>
</head>
<body>

        <form id="htmlForm" action="customers.php" method="post">
                First:  <input type="text" name="first" /><br/>
                Last:   <input type="text" name="last" /><br/>
                Phone:  <input type="text" name="phone" /><br/>
                Mobile: <input type="text" name="mobile" /><br/>
                Fax:    <input type="text" name="fax" /><br/>
                Email:  <input type="text" name="email" /><br/>
                Web:    <input type="text" name="web" /><br/><br/>
            <input id="btnReload" value="Add Customer" type="submit"> 
        </form>
        <br />

<!---------------------
<-- CUSTOMER TABLE ----
----------------------->

    <?php
    //MySQL Database Connect
     include 'customers.php';
    ?>

</body>
</html>

customers.php

    <?php

    //MySQL Database Connect
    include 'config.php';

    $_POST['first'] = "undefine"; 
    $_POST['last'] = "undefine"; 
    $_POST['phone'] = "undefine"; 
    $_POST['mobile'] = "undefine"; 
    $_POST['fax'] = "undefine"; 
    $_POST['email'] = "undefine"; 
    $_POST['web'] = "undefine"; 

    $sql="INSERT INTO contacts (first, last, phone, mobile, fax, email, web)
    VALUES
    ('$_POST[first]','$_POST[last]','$_POST[phone]','$_POST[mobile]','$_POST[fax]','$_POST[email]','$_POST[web]')";

        if (!mysql_query($sql,$db))
            {
                die('Error: ' . mysql_error());
            }

            $query="SELECT * FROM contacts";
            $result=mysql_query($query);


?>
<h2>Customers Table</h2>
<?php

    $query="SELECT * FROM contacts";
    $result=mysql_query($query);

?>

    <table cellpadding="0" cellspacing="0" border="0" class="bordered-table zebra-striped" id="datatable">
        <thead>
            <tr>
                <th>First</th>
                <th>Last</th>
                <th>Phone</th>
                <th>Mobile</th>
                <th>Fax</th>
                <th>Email</th>
                <th>Web</th>
            </tr>
        </thead>

    <?php   
    $num=mysql_numrows($result);

    $i=0;
    while ($i < $num) {

        $first=mysql_result($result,$i,"first");
        $last=mysql_result($result,$i,"last");
        $phone=mysql_result($result,$i,"phone");
        $mobile=mysql_result($result,$i,"mobile");
        $fax=mysql_result($result,$i,"fax");
        $email=mysql_result($result,$i,"email");
        $web=mysql_result($result,$i,"web");

        ?>
            <tbody>
                <tr>
                    <td><?php echo $first ?></td>
                    <td><?php echo $last ?></td>
                    <td><?php echo $phone ?></td>
                    <td><?php echo $mobile ?></td>
                    <td><?php echo $fax ?></td>
                    <td><?php echo $email ?></td>
                    <td><?php echo $web ?></td>
                </tr>

    <?php
    $i++;
    }

?>

config.php

    <?php
$mysql_hostname = "localhost";
$mysql_user = "root";
$mysql_password = "";
$mysql_database = "address";
$prefix = "";
$db = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Could not connect database");
mysql_select_db($mysql_database, $db) or die("Could not select database");
?>

It's working reasonably well at the moment the form is updating the table when I input data but everytime I reload the page a blank record gets inserted into the database. Could someone please advise on the best solution to this problem.

Also, you [really, really shouldn't use the `mysql_*` functions](http://www.php.net/manual/en/mysqlinfo.api.choosing.php). Whenever you need to insert something that is user-submitted, you should use [parameterized queries](http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php). — Levi Morrison, Jan 29 '12 at 19:17

score 1 · Accepted Answer · edited Jan 29 '12 at 19:12

1

Empty rows are being inserted because your insert code runs even if the form was not submitted. You can fix this by checking to make sure that it is a POST operation before inserting:

include 'config.php';

if($_SERVER['REQUEST_METHOD'] == 'POST') {
    // your insertion code here
}

//or even better:
if (!empty($_POST)) {
    //insertion code
}

$query="SELECT * FROM contacts";
$result=mysql_query($query);
...

However, you have a more serious problem in that you are not sanitizing your input. What if somebody enters '); DELETE FROM contacts; in your web field? This is known as an SQL Injection attack.

To avoid it, you should either use prepared statements or use mysql_real_escape_string to protect your application from this sort of attack.

edited Jan 29 '12 at 19:12

Levi Morrison

19,116
7
65
85

answered Jan 29 '12 at 18:55

Jason Burbage

133
7

jburbage, if he's loading up the whole table, I'm guessing it's an admin only section. If it isn't, then this is a BIG deal. – Levi Morrison Jan 29 '12 at 19:04
@Levi In an admin section injection is less of a threat, but it could still be a threat, and you still want to sanitize to allow apostrophes (et al) in your fields. Although I brought it up mainly because he appears to be doing this as an educational exercise, and there's no better time to learn about SQL Injection! – Jason Burbage Jan 29 '12 at 19:10
Hey guys, much appreciated, that has solved the issue. Your both correct the table will be loaded in an admin section and it is an educational exercise but as you suggest it's as good a time as any to learn about SQL injection. I'll investigate guarding my application against such attacks. Thanks for all the help. – php_d Jan 29 '12 at 19:33

Php / Jquery Forms & Datatables

1 Answers1