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?

Rafael · Accepted Answer · 2013-04-30 13:48:48Z

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

Community · Accepted Answer · 2017-05-23 11:58:11Z

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, 2017 at 11:58

CommunityBot

11 silver badge

answered Apr 30, 2013 at 11:52

Sander_P

1,8271 gold badge13 silver badges39 bronze badges

1 Comment

user1620090 Over a year ago

Yes you are right, I was searching for ASP javacsript and returned little result then, the link posted was also very helpful. Many thanks

Kerry Cakebread · Accepted Answer · 2013-04-30 13:47:09Z

0

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/

answered Apr 30, 2013 at 13:47

Kerry Cakebread

711 silver badge7 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related