Creating Connection Object

Run a SQL query in cfscript by creating a connection string by accessing the servicFactory
If we want to execute a Query like underneath in cfscript:–

SELECT UserName,Email,UserType FROM UserInfo
WHERE UserName = 
AND UserID = 

NOTE: Here connceting to database and SQL injection(SQL Attack) is control by coldfusion attribute ie datasource and cfqueryparam in cfquery tag respectively so there is no need of creating connnection string explicit but in cfscript if we want to create connection to database via datasource then we need to create connection string or we can use New Query() function.

So here I am going to do stuff by creating connection string.

// First, we need to get access to the ColdFusion service
// factory. This is a Java object that we can instantiate.
objFactory = CreateObject(
"java",
"coldfusion.server.ServiceFactory"
);

// Get the Data Source service from the service factory.
objDataService = objFactory.DataSourceService;

// The data service object has access to all data sources
// running on the server. Let's get a connection to our
// datasource before running the query.
objDataSource = objDataService.GetDataSource(
"PollSystemAlpha"
);

// Open the connection. Here, we have the option to pass
// in a username and password. Since I am on the dev
// server, no need to do so.
objConnection = objDataSource.GetConnection(
// USERNAME, UserINfo if needed //
);

// Prepare the SQL statement that you want to run. Much
// harder than the CFQuery tag, but not impossible.
objStatement = objConnection.PrepareStatement(
"SELECT " &
"UserName, " &
"Email, " &
"UserType " &
"FROM " &
"UserInfo" &
"WHERE " &
"UserName=" &
"form.username" &
"AND" &
"UserID=," &
"session.UserID"
);

// NOTE:I break up the SQL above to emulate the look and
// feel of a CFQuery tag. This would be the SAME EXACT as
// just doing:
// In Cfquery Tag.


// Execute the prepared SQL statement. This line alone returns
// the java class: "macromedia.jdbc.base.BaseResultSet". In order
// to work with this most effectively, we have to turn it into
// the Java class "coldfusion.sql.QueryTable". This is the class
// of object that is returned from the CFQuery tag, and the type
// of object that we are all used to working with.
objResults = CreateObject(
"java",
"coldfusion.sql.QueryTable"
).Init( objStatement.ExecuteQuery() );

// Close the connection.
objConnection.Close();

So here I have cover the creation of the connection string and execute the Query but SQL attack has not covered hence for SQL Injection in cfscript please follow the next tip “SQL Injection in cfscript”.

Hope It will help those who used to write the code in cfscript for faster execution.