Skip to main content

Posts

Showing posts with the label SQL

Using CFQueryParam in Order By Clause

With all of the SQL Injection attacks going on in the ColdFusion world I thought that it may be beneficial to show everyone a way that I know of to have cfqueryparam'd 'Order By' clauses. If anyone knows of other ways to accomplish this, please feel free to post a comment on how to do so. <cfquery name="GetData" datasource="#APPLICATION.DataSource#"> DECLARE @param varchar(25),    @dataID varchar(25),    @sql nvarchar(700); SELECT @param=<cfqueryparam cfsqltype="cf_sql_varchar" value="#VARIABLES.orderby#">,    @dataID=<cfqueryparam cfsqltype="cf_sql_varchar" value="#VARIABLES.DataID#">,    @sql = 'SELECT ID,       Phone_Number,       First_Name,       Last_Name,       Address    FROM Table WITH (NOLOCK)    WHERE DataID = ' + @dataID + 'Order by ' + @param + ...

Combining Select and Insert into the same CFQuery tag

So you want to grab some values and insert them into a table. Here is the hard way. <cfquery name="selectQuery" datasource="#application.dsn.db2#">     SELECT Setting1,Setting2,Setting3     FROM defaultSettings     WHERE ID = 101 </cfquery> <cfquery name="insertQuery" datasource="#application.dsn.db#"> INSERT INTO Settings     (TS,     Name,     Setting1,     Setting2,     Setting3) VALUES(GetDate(),     'Test',     <cfqueryparam cfsqltype="cf_sql_varchar" value="#selectQuery.Setting1#">,     <cfqueryparam cfsqltype="cf_sql_varchar" value="#selectQuery.Setting2#">,     <cfqueryparam cfsqltype="cf_sql_varchar" value="#selectQuery.Setting3#">) </cfquery> Here is the easier way, combining both Select and Insert i...

Using SQL's Scope_Identity

So you want to get the ID(PrimaryKey) of the last record inserted into the database without having to do a second cfquery block. Here is an example of doing it the hard way. <cfset value1 = 2007> <cfset myuuid = CreateUUID()> <cfquery name="insert" datasource="#APPLICATION.DSN.DB#">     INSERT INTO table(column1,column2)     VALUES(#VARIABLES.value1#, '#VARIABLES.myUUID#') </cfquery> <cfquery name="select" datasource="#APPLICATION.DSN.DB#">     SELECT ID     FROM table     WHERE column2=<cfqueryparam cfsqltype="cf_sql_varchar" value="#VARIABLES.myUUID#"/> </cfquery> You would then reference the ID as select.ID. The easier way. <cfquery name="insert" datasource="#APPLICATION.DSN.DB#">     INSERT INTO table(column1,column2)     VALUES(#VARIABLES.value1#, '#VARIABLES.myUUID#')     SELE...