Using where clause with javascript variable

Question

Hello all this is my first question on stack overflow. I am developing an app with phonegap, with using webSQL and javascript. In my App there is a login form which takes two inputs as: username and password. when user fills these values and clicks on Login these values are checked in database table which have multiple rows. for checking values I am using this query-

var email=document.getElementById("uname").value;
tx.executeSql('SELECT * FROM USERINF Where Email=email', [], MatchPass, errorCB1);

But the query is not working with where clause with a java script variable. I have also tried many syntexes such as

where Email=@email
where Email="+email+"

and many more but they all not worked.

Is it possible to use a javascript variable with where clause?? If yes please tell me how. if not please suggest me any other way to accomplish the task. thanks in advance.

this is my full JavascriptCode

// JavaScript Document
var db=window.openDatabase("CakeViewer", "1.0", "Cake Viewer", 2*1024*1024);

function login()
{

db.transaction(matchcred)

function matchcred(tx)
{
    var eml=document.getElementById("uname").value;
     tx.executeSql('SELECT * FROM USERINF Where Email=eml', [], MatchPass, errorCB1); 
 //problem is in above line-- the query is not executed with variable where clause. But it works fine if I use where Email="abc@abc.com"

}


function MatchPass(tx, results)
{
    var orgnalPass=results.rows.item(0).Password;   
    var userinputedPass=document.getElementById("pass");

    if(orgnalPass==userinputedPass.value)
    {
        window.location.href='HomePage.html';
    }
    else
    {
        errorCB();
    }
}

function errorCB(tx,err)
{
    alert("User Name or Password is not valid !");
    document.getElementById("uname").value="";
    document.getElementById("pass").value="";
}

function errorCB1()
{
    alert("Query failed");
}

function errorCB2(tx,err)
{
    alert("errorCB2"+err);
}
}

score 10 · Answer 1 · answered Sep 05 '12 at 06:53

10

The easiest way is to use a questionmark (?) to specify the variables.

For example:

tx.executeSql('SELECT Data from Table Where something = ?', [email], MatchPass, errorCB1);

The ?'s get parsed from the left to the right and match the variables between [] from left to right.

Another example:

tx.executeSql('SELECT Data from Table Where email = ? AND username=?', [email, username], MatchPass, errorCB1);

answered Sep 05 '12 at 06:53

Jens

319
3
10

Thanks @Jens for this alternate answer – Hemant Patel Sep 06 '12 at 07:33
+1 as this answer also helps in preventing SQL injection – krishwader Jan 20 '14 at 09:37

score 0 · Answer 2 · answered Aug 03 '12 at 06:21

0

tx.executeSql("SELECT * FROM USERINF Where Email='"+email+"'" ,

answered Aug 03 '12 at 06:21

SoWa

314
4
12

Thanks for your answer SoWa. but '"+email+"' not worked, instead "'+ email+ '" worked. – Hemant Patel Aug 03 '12 at 07:26

score -1 · Accepted Answer · answered Aug 03 '12 at 06:25

-1

To get the value of the js variable email in the query you need to use + operator to concatenate. Also wrap the value with quotes as value may be string

tx.executeSql('SELECT Data from Table Where something = "'+ email+ '"', [], MatchPass, errorCB1);

answered Aug 03 '12 at 06:25

Mithun Satheesh

27,240
14
77
101

1

Yeah, but, assuming email comes from the user, this is a great way to get SQL injection unless you escape any potential quotes in the email value. Also, does that SQL syntax really wrap strings in double quotes? Seems wrong to me. – ErikE Aug 07 '12 at 04:34
+1 to ErikE's comment about the security issue introduced by such a practice. Please please please don't advocate for (or accept an answer that proposes) using raw SQL statements like this. – danielpops Sep 27 '17 at 17:49

Using where clause with javascript variable

3 Answers3