How to SELECT, INSERT, UPDATE and DELETE data from SQLite with PHP (and HTML)

Question

I do have a SQLite Web Interface, that should SELECT, INSERT, UPDATE and DELETE data from a SQLite table. Unfortunately, only the SELECT query seems to work. I cannot INSERT, UPDATE or DELETE.

sshTunnel.sqlite --> mydata

CREATE TABLE "mydata" ("ID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "SSH_USER" VARCHAR, "SSH_IP" VARCHAR, "SSH_PORT" VARCHAR, "SSH_PW" VARCHAR, "SSH_KEYFILE" VARCHAR, "TUNNEL_LOCAL_INTERFACE" VARCHAR, "TUNNEL_LOCAL_PORT" VARCHAR, "TUNNEL_REMOTE_IP" VARCHAR, "TUNNEL_REMOTE_PORT" VARCHAR, "DESCRIPTION" VARCHAR)

Here are my code snippets...

index.php

<script>
$(document).ready(function()
{
    function fetch_data()
    {
        $.ajax({
            url:"select.php",
            method:"POST",
            success:function(data)
            {
                $('#live_data').html(data);
            }
        });
    }
    fetch_data();

    $(document).on('click', '#btn_add', function()
    {
        var SSH_USER = $('#SSH_USER').text();
        var SSH_IP = $('#SSH_IP').text();
        var SSH_PORT = $('#SSH_PORT').text();
        var SSH_PW = $('#SSH_PW').text();
        var SSH_KEYFILE = $('#SSH_KEYFILE').text();
        var TUNNEL_LOCAL_INTERFACE = $('#TUNNEL_LOCAL_INTERFACE').text();
        var TUNNEL_LOCAL_PORT = $('#TUNNEL_LOCAL_PORT').text();
        var TUNNEL_REMOTE_IP = $('#TUNNEL_REMOTE_IP').text();
        var TUNNEL_REMOTE_PORT = $('#TUNNEL_REMOTE_PORT').text();
        var DESCRIPTION = $('#DESCRIPTION').text();

        $.ajax({
            url:"insert.php",
            method:"POST",
            data:{SSH_USER:SSH_USER, SSH_IP:SSH_IP, SSH_PORT:SSH_PORT, SSH_PW:SSH_PW, SSH_KEYFILE:SSH_KEYFILE, TUNNEL_LOCAL_INTERFACE:TUNNEL_LOCAL_INTERFACE, TUNNEL_LOCAL_PORT:TUNNEL_LOCAL_PORT, TUNNEL_REMOTE_IP:TUNNEL_REMOTE_IP, TUNNEL_REMOTE_PORT:TUNNEL_REMOTE_PORT, DESCRIPTION:DESCRIPTION},
            dataType:"text",
            success:function(date)
            {
                alert(data);
                fetch_data();
            }
        });
    });

    $(document).on('click', '.btn_edit', function()
    {
        var SSH_USER = $(this).data("id1");
        var SSH_IP = $(this).data("id2");
        var SSH_PORT = $(this).data("id3");
        var SSH_PW = $(this).data("id4");
        var SSH_KEYFILE = $(this).data("id5");
        var TUNNEL_LOCAL_INTERFACE = $(this).data("id6");
        var TUNNEL_LOCAL_PORT = $(this).data("id7");
        var TUNNEL_REMOTE_IP = $(this).data("id8");
        var TUNNEL_REMOTE_PORT = $(this).data("id9");
        var DESCRIPTION = $(this).data("id10");

        if(confirm("Are you sure you want to edit this?"))
        {
            $.ajax({
                url:"edit.php",
                method:"POST",
                data:{SSH_USER:SSH_USER, SSH_IP:SSH_IP, SSH_PORT:SSH_PORT, SSH_PW:SSH_PW, SSH_KEYFILE:SSH_KEYFILE, TUNNEL_LOCAL_INTERFACE:TUNNEL_LOCAL_INTERFACE, TUNNEL_LOCAL_PORT:TUNNEL_LOCAL_PORT, TUNNEL_REMOTE_IP:TUNNEL_REMOTE_IP, TUNNEL_REMOTE_PORT:TUNNEL_REMOTE_PORT, DESCRIPTION:DESCRIPTION},
                dataType:"text",
                success:function(date)
                {
                    alert(data);
                    fetch_data();
                }
            });
        }
    });

    $(document).on('click', '.btn_delete', function()
    {
        var id = $(this).data("id12");
        if(confirm("Are you sure you want to delete this?"))
        {
            $.ajax({
                url:"delete.php",
                method:"POST",
                data:{id:id},
                dataType:"text",
                success:function(date)
                {
                    alert(data);
                    fetch_data();
                }
            });
        }
    });
});
</script>

...

<div id="live_data"></div>

select.php

<?php
$db = new SQLite3('sshTunnel.sqlite');
$results = $db->query('SELECT * FROM mydata');
$output = '';
$output .= '<div class="table-responsive">
                <table class="table table-bordered">
                    <tr>
                        <th>ID</th>
                        <th>SSH_USER</th>
                        <th>SSH_IP</th>
                        <th>SSH_PORT</th>
                        <th>SSH_PW</th>
                        <th>SSH_KEYFILE</th>
                        <th>TUNNEL_LOCAL_INTERFACE</th>
                        <th>TUNNEL_LOCAL_PORT</th>
                        <th>TUNNEL_REMOTE_IP</th>
                        <th>TUNNEL_REMOTE_PORT</th>
                        <th>DESCRIPTION</th>
                        <th>Editieren</th>
                        <th>Löschen</th>
                    </tr>';
while ($row = $results->fetchArray())
{
    $output .= '<tr>
                    <td>'.$row["ID"].'</td>
                    <td class="SSH_USER" data-id1="'.$row["ID"].'" contenteditable="true">'.$row["SSH_USER"].'</td>
                    <td class="SSH_IP" data-id2="'.$row["ID"].'" contenteditable="true">'.$row["SSH_IP"].'</td>
                    <td class="SSH_PORT" data-id3="'.$row["ID"].'" contenteditable="true">'.$row["SSH_PORT"].'</td>
                    <td class="SSH_PW" data-id4="'.$row["ID"].'" contenteditable="true">'.$row["SSH_PW"].'</td>
                    <td class="SSH_KEYFILE" data-id5="'.$row["ID"].'" contenteditable="true">'.$row["SSH_KEYFILE"].'</td>
                    <td class="TUNNEL_LOCAL_INTERFACE" data-id6="'.$row["ID"].'" contenteditable="true">'.$row["TUNNEL_LOCAL_INTERFACE"].'</td>
                    <td class="TUNNEL_LOCAL_PORT" data-id7="'.$row["ID"].'" contenteditable="true">'.$row["TUNNEL_LOCAL_PORT"].'</td>
                    <td class="TUNNEL_REMOTE_IP" data-id8="'.$row["ID"].'" contenteditable="true">'.$row["TUNNEL_REMOTE_IP"].'</td>
                    <td class="TUNNEL_REMOTE_PORT" data-id9="'.$row["ID"].'" contenteditable="true">'.$row["TUNNEL_REMOTE_PORT"].'</td>
                    <td class="DESCRIPTION" data-id10="'.$row["ID"].'" contenteditable="true">'.$row["DESCRIPTION"].'</td>
                    <td><button type="button" name="edit_btn" data-id11="'.$row["ID"].'" class="btn btn-xs btn-warning btn-block btn_edit">Editieren</button></td>
                    <td><button type="button" name="delete_btn" data-id12="'.$row["ID"].'" class="btn btn-xs btn-danger btn-block btn_delete">Löschen</button></td>
                </tr>';
}
$output .= '<tr>
                <td></td>
                <td id="SSH_USER" contenteditable="true"</td>
                <td id="SSH_IP" contenteditable="true"</td>
                <td id="SSH_PORT" contenteditable="true"</td>
                <td id="SSH_PW" contenteditable="true"</td>
                <td id="SSH_KEYFILE" contenteditable="true"</td>
                <td id="TUNNEL_LOCAL_INTERFACE" contenteditable="true"</td>
                <td id="TUNNEL_LOCAL_PORT" contenteditable="true"</td>
                <td id="TUNNEL_REMOTE_IP" contenteditable="true"</td>
                <td id="TUNNEL_REMOTE_PORT" contenteditable="true"</td>
                <td id="DESCRIPTION" contenteditable="true"</td>
                <td colspan="13"><button type="button" name="btn_add" id="btn_add" class="btn btn-xs btn-block btn-success">Hinzufügen</button></td>
            </tr>
        </table>
    </div>';
echo $output;
$db->close();
?>

insert.php

<?php
$db = new SQLite3('sshTunnel.sqlite');
$db->exec('INSERT INTO mydata(  SSH_USER, 
                                SSH_IP, 
                                SSH_PORT, 
                                SSH_PW, 
                                SSH_KEYFILE, 
                                TUNNEL_LOCAL_INTERFACE, 
                                TUNNEL_LOCAL_PORT, 
                                TUNNEL_REMOTE_IP, 
                                TUNNEL_REMOTE_PORT, 
                                DESCRIPTION) 
                                VALUES( '".$_POST["SSH_USER"]."', 
                                                '".$_POST["SSH_IP"]."', 
                                                '".$_POST["SSH_PORT"]."', 
                                                '".$_POST["SSH_PW"]."', 
                                                '".$_POST["SSH_KEYFILE"]."', 
                                                '".$_POST["TUNNEL_LOCAL_INTERFACE"]."', 
                                                '".$_POST["TUNNEL_LOCAL_INTERFACE"]."', 
                                                '".$_POST["TUNNEL_LOCAL_PORT"]."', 
                                                '".$_POST["TUNNEL_REMOTE_IP"]."', 
                                                '".$_POST["TUNNEL_REMOTE_PORT"]."', 
                                                '".$_POST["DESCRIPTION"]."')');
$db->close();
?>

edit.php

<?php
$db = new SQLite3('sshTunnel.sqlite');
$db->exec("UPDATE mydataSET     SSH_USER='".$_POST["SSH_USER"]."', 
                                SSH_IP='".$_POST["SSH_IP"]."', 
                                SSH_PORT='".$_POST["SSH_PORT"]."', 
                                SSH_PW='".$_POST["SSH_PW"]."', 
                                SSH_KEYFILE='".$_POST["SSH_KEYFILE"]."', 
                        TUNNEL_LOCAL_INTERFACE='".$_POST["TUNNEL_LOCAL_INTERFACE"]."', 
                            TUNNEL_LOCAL_PORT='".$_POST["TUNNEL_LOCAL_PORT"]."', 
                                TUNNEL_REMOTE_IP='".$_POST["TUNNEL_REMOTE_IP"]."', 
                                TUNNEL_REMOTE_PORT='".$_POST["TUNNEL_REMOTE_PORT"]."', 
                                DESCRIPTION='".$_POST["DESCRIPTION"]."' 
                                WHERE ID='".$_POST["ID"]."'");
$db->close();
?>

delete.php

<?php
$db = new SQLite3('sshTunnel.sqlite');
$db->exec('DELETE FROM mydata WHERE ID='".$_POST["ID"]."'');
$db->close();
?>

Sergio Bernardo · Accepted Answer · 2016-06-23 10:10:56Z

1

Are you sure your scripts are compiling and running? I spot a strange use of quotes in insert.php: you open the string with single quote and them use double quote to terminate and concatenate... same for delete.php.

Also, make sure you "addslashes" to all the data you place in the query. If a single quote enters any content, all your queries will fail and, of course, security matters.

-- Sérgio

answered Jun 23, 2016 at 10:10

Sergio Bernardo

3682 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sjantke Over a year ago

Searching for "how to concatenate with php post" made it. Thanks for your help, Sérgio!

Collectives™ on Stack Overflow

How to SELECT, INSERT, UPDATE and DELETE data from SQLite with PHP (and HTML)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related