ASP javascript how to create an SQL SELECT statement using prepared statement

Question

I am using ASP javascript to select from a MySQL database using a parameter passed by the user. I would like to do this using a prepared statement. I have seen examples in VB script but can't figure it out in ASP JS. I would normally do it in the following way:

var adoConnection = Server.CreateObject("ADODB.Connection");
adoConnection.Open("dsn=my-dsn;uid=userid;pwd=password;");


var getAdmin = "SELECT * FROM users WHERE username = '"+String(Request.QueryString("username"))+"'";
var rsAdmin = adoConnection.Execute(getAdmin);

I would like to change this to pass the user data in a safer way, can anyone help?

score 2 · Accepted Answer · answered Apr 30 '13 at 13:48

to parametrize correctly in ASP your Queries, you need to use "ADODB.Command" to execute your queries instead of using ADODB.Connection directly. ADODB.Command has method named ".CreateParameter()" that permits that you want.

Example code

'-------------------------------------------------------------------'
var oCmd = Server.CreateObject("ADODB.Command")
var sSQL = "SELECT username, action FROM userlog WHERE event_date < ? ;";
oCmd.CommandText = sSQL
oCmd.ActiveConnection= oConn
'-------------------------------------------------------------------'
var oPar = oCmd.CreateParameter("event_date",7,1,,dDate); 'Date
oCmd.Parameters.Append(oPar);
'-------------------------------------------------------------------'

.... do this until you have all the parameters appended and ....

var oRS = oCmd.Execute();

and you manipule the recordset as you wish

Aditional resources

ADODB Documentation

MSDN Example

score 0 · Answer 2 · edited May 23 '17 at 11:58

0

ASP javascript is usually reffered to as JScript. If you search for '[jscript] [mysql]' on stackoverflow it will show you a question which will probably answer your question:

ADODB Command failing Execute with parameterised SQL query

You could also google 'msdn jscript ado' for additional samples.

edited May 23 '17 at 11:58

Community

1
1

answered Apr 30 '13 at 11:52

Sander_P

1,787
1
13
37

Yes you are right, I was searching for ASP javacsript and returned little result then, the link posted was also very helpful. Many thanks – user1620090 Apr 30 '13 at 14:58

score 0 · Answer 3 · answered Apr 30 '13 at 13:47

Although calling into a database directly from browser-side code isn't a preferred method of retrieving data into the page (most folks prefer AJAX/JSON requests these days...), you could definitely improve the security of your code by converting the SQL statement to a stored procedure call.

For details, see http://andrewu.co.uk/clj/stored_procedures_with_jscript/

ASP javascript how to create an SQL SELECT statement using prepared statement

3 Answers3