Sqlite3 putting input field values into insert statement

Question

I have sqlite database with 4 columns

Name
Age
bloodGroup
lastdate

and 4 input fields and saving button as following:

<input type="text" name="" id="patientName">
<input type="number" name="" id="PatientAge">
<input type="text" name="" id="PatientBloodGroup">
<input type="date" name="" id="PatientLastDate">
<button id="savebtn"> Save </button>

and i used the following javascript code to take the input values and insert them into the columns of the database :

<script type="text/javascript">
    document.getElementById('savebtn').addEventListener('click', saveFunction);
    function saveFunction(){
        const sqlite3 = require('sqlite3').verbose();
        let db = new sqlite3.Database('./database_name');
        var patientName = document.getElementById('patientName').value;
        var patientAge = document.getElementById('patientAge').value;
        var patinetBloodGroup =   document.getElementById('patientBloodGroup').value;
        var PatientLastDate = document.getElementById('patientLastDate').value;

        db.run(`INSERT INTO info(Name, Age, bloodGroup, lastdate) VALUES(patientName, patientAge, patientBloodGroup, PatientLastDate), function(err) {
            if (err) {
                return console.log(err.message);
            }
            console.log(`A row has been inserted with rowid ${this.lastID}`);
        });
        db.close();
    } 
</script>

When the program is run it gives this error message :

SQLITE_ERROR: no such column: patientName.

what is your db schema? it says the table info doesn't have the column Name in it — Aritra Chakraborty
– Aritra Chakraborty, Commented Aug 3, 2018 at 22:58
I'm sorry i have written the error message by mistake. The actual error message as the following : SQLITE_ERROR: no such column: patientName. @AritraChakraborty — bishoy
– bishoy, Commented Aug 3, 2018 at 23:20
The problem is that you are running a literal SQL string without substituting the variable values in. Most databases will interpret this as an instruction to get the value from the field named patientName which clearly isn't what you want. Try something like the 'Insert Multiple Records' part on this page: w3schools.com/nodejs/nodejs_mysql_insert.asp (obviously, you only want one record. — GregHNZ
– GregHNZ, Commented Aug 3, 2018 at 23:58
when i try the example in w3school the error changes to : SQLITE_ERROR: 1 values for 4 columns @GregHNZ — bishoy
– bishoy, Commented Aug 4, 2018 at 0:39

Aritra Chakraborty · Accepted Answer · 2018-08-04 09:31:25Z

It will seem silly but you're not enclosing the values with a quote and also not evaluating the variables. The INSERT INTO query will be in the form of

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

So change your db query to:

db.run(`INSERT INTO info(Name, Age, bloodGroup, lastdate) VALUES('${patientName}', '${patientAge}', '${patientBloodGroup}', '${PatientLastDate}')`), function(err) {
    if (err) {
        return console.log(err.message);
    }
    console.log(`A row has been inserted with rowid ${this.lastID}`);
});

Now, this code is obviously susceptible to SQL injection. You should use prepared statement

db.run(`INSERT INTO info(Name, Age, bloodGroup, lastdate) VALUES(?, ?, ?, ?)`, patientName, patientAge, patientBloodGroup, PatientLastDate), function(err) {
    if (err) {
        return console.log(err.message);
    }
    console.log(`A row has been inserted with rowid ${this.lastID}`);
});

Collectives™ on Stack Overflow

Sqlite3 putting input field values into insert statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related