CODEWITHSUNDEEP
asp-classic
user1620090
user1620090

Reputation: 549

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

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?

Upvotes: 1

Views: 3883

Answers (3)

Rafael
Rafael

Reputation: 3111

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

Upvotes: 2

Kerry Cakebread
Kerry Cakebread

Reputation: 71

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/

Upvotes: 0

Sander_P
Sander_P

Reputation: 1835

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.

Upvotes: 0

Related Questions