Search code examples
asp-classic

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?

Solution

  • 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