Home » Tutorials » ColdFusion » With SQL

With SQL

First off, you need to be able to write SQL queries before you work with them in ColdFusion. Once you are able to write them, it is just a matter of putting them in the right ColdFusion Tags, and adding the right arguments. The starting tag for queries is <cfquery> and the closing tag is </cfquery>. A few arguments can be added, but we will stick with the simple ones. First, your awesome query needs a name for later reference. You can use name=”” where your name variable will be within those quotes to set the query name in ColdFusion. Next, we need to know where to find the database. Now, I can’t tell you exactly how to do this. You need to figure out how to create a connection string to a database. It is somewhat complicated and varies based on what database you are wanting to use. You can use the power of Google to figure that out. Once you figure it out, set that equal to the variable myDatabase and we will just use that variable to reference our database.

Returning Data from a Database

Example
<cfoutput>
    <cfquery name="myQuery" datasource="#myDatabase#">
        SELECT column1, column2 
        FROM someTable
    </cfquery>
    <cfloop query="myQuery">
        #option_id# - #reason# <br/>
    </cfloop>
</cfoutput>

Notice how when I was using the <cfloop>, I return #myQuery.column1# and #myQuery.column2#, these are both column names in the someTable table.

Inserting Data into a Database

Now, I don’t want to destroy your database, so I am just going to show you an example for the INSERT. While not as dangerous as a DELETE, always try to be careful that you don’t insert a million junk rows. We are going to input some variables in our ColdFusion code into the database.

Example
<cfparam name="goodReason" default="" type="string">
<cfset goodReason = "Chasing Squirrels">
<cfoutput>
    <cfquery name="myQuery" datasource=" #myDatabase#">
        INSERT INTO someTable (column1)
        VALUES(<cfqueryparam value="#goodReason#" cfsqltype="CF_SQL_VARCHAR">)
    </cfquery>
</cfoutput>

Since you already know some SQL, you could tell the INSERT query was going to be a little different than the SELECT query. Normally, you will not have the <cfset goodReason = “Chasing Squirrels”> in the statement because it should be set somewhere else by the user input. Our <cfparam name=”goodReason” default=”” type=”string”> is searching for GET and POST data that might contain our variable goodReason, if it cannot find it then goodReason‘s value is set to “” (nothing). The type is important to help stop users from switching what it is supposed to be. While it isn’t that helpful with a string, consider if goodReason was supposed to be a number, but the user switched it to a string. Obviously, that wouldn’t be good for business. The same thing is happening down in the actual query. The <cfqueryparam value=”#goodReason#” cfsqltype=”CF_SQL_VARCHAR”> is the last line of defense against SQL injections attacks in ColdFusion. It makes sure that the value is what type of value we want it to be.

Deleting Data from a Database

I highly recommend that you back this data up somewhere else before deleting it or consider making a column in the database that is something like “isVisible”, where you could get the records. The idea would be that you would only show records that have isVisible set to 1, and the records set to 0 would show you past records so you don’t lose any data. But, if you must annihilate the data, we can cover that too. Again, I am not going to destroy our precious database, but I will show you an example.

Example
<cfparam name="goodReasonGoneWrong" default="" type="string">
<cfset goodReasonGoneWrong = "Chasing Squirrels">
<cfoutput>
    <cfquery name="myQuery" datasource=" #myDatabase#">
        DELETE FROM someTable
        WHERE column1 = <cfqueryparam value="#goodReasonGoneWrong#" cfsqltype="CF_SQL_VARCHAR">
    </cfquery>
</cfoutput>

Again, the <cfset goodReasonGoneWrong = “Chasing Squirrels”> will usually not be there. What is the point in setting a variable right before inserting it into a database? We would be better off just putting VALUES(‘Chasing Squirrels’) instead. Now that I have beaten that dead horse, you probably guessed that the DELETE query would also be different from the rest. We set the column column1 equal to our <cfqueryparam value=”#goodReasonGoneWrong#” cfsqltype=”CF_SQL_VARCHAR”> to escape SQL injection. So, this query actually deletes anything in our table that has ‘Chasing Squirrels’ in the column1 column.



Link/cite this page

If you use any of the content on this page in your own work, please use the code below to cite this page as the source of the content.

  • Stewart, Suzy. "With SQL". After Hours Programming. Accessed on October 25, 2024. https://www.afterhoursprogramming.com/tutorial/coldfusion/with-sql/.

  • Stewart, Suzy. "With SQL". After Hours Programming, https://www.afterhoursprogramming.com/tutorial/coldfusion/with-sql/. Accessed 25 October, 2024.

  • Stewart, Suzy. With SQL. After Hours Programming. Retrieved from https://www.afterhoursprogramming.com/tutorial/coldfusion/with-sql/.



Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.