- Download the driver from Microsoft.
- Install the driver. By default, this installs in C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC on Windows platforms.
- In the ColdFusion Administrator, click on the “Java and JVM” page. Add the directory in which the mssqlserver.jar resides to the Class Path field. By default, this is C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib on Windows platforms.
- Click Submit Changes to update Java and JVM Settings for ColdFusion MX.
- Restart the ColdFusion MX Application Server service. Note: On Unix platforms, from the cf_root/runtime/bin directory, run
coldfusion restart. - Use the ColdFusion Administrator Data Sources page to add a new data source for the Microsoft JDBC driver:
For example:- Name the data source ps_db where it will connect to the server named ps-db where MicrosoftSQL Server resides.
- Select Other in the driver field to tell ColdFusion you will describe another type of driver.
- Click Add to add the data source and display the default driver window for this driver.
- Enter the following in the JDBC URL field:jdbc:microsoft:sqlserver://ps-db:1433;DatabaseName=NorthWind
Where ps-db is the name of the database server.
Where 1433 is the port number for the data source connection.
Where NorthWind is the database you are connecting to.
Note: The information in this field is case-sensitive.
- Enter the following in the Driver Class field:com.microsoft.jdbc.sqlserver.SQLServerDriver
Note: The information in this field is case-sensitive.
- Enter a valid username and password in the Username and Password fields.Where the username and password are defined as user account information on the database server.
- (Optional) Enter the name of the driver in the Driver Name field.
- Click Submit to apply the changes to the data source.
“ColdFusion”目录存档
Configuring ColdFusion MX to use the Microsoft SQL Server JDBC driver
2008年08月16日，星期六ColdFusion Query of Queries Order By Case Sensitivity Fun
2007年08月29日，星期三Have you ever tried using ORDER BY in a query of queries in ColdFusion, well come to find out its case sensitive, and I need it to be case insensitive.I have a query of queries in ColdFusion. In Windows my original query sorted it self out just fine, because it was listing the underlying File system items which are case insenstive. When I deployed the code on Linux of course the original query sorts differently. The code actually does another query on the first query to add some other fields. But when I did a ORDER BY Name on this query of queries it was Case Sensitive, and thus a capital “Z” came before a lowercase “a”. You can not use the UPPER or LOWER query functions on the ORDER BY clause but you can use them when defining a field.
This led me to the solution below.
XML:
-
SELECT Name,
-
UPPER( Name ) as DName
-
FROM myList
-
WHERE type = ‘Dir’
-
ORDER BY DName
Is there a built in way in ColdFusion to do this with out create a new column on the query?
Download JPG’s with ColdFusion from a Linux server
2007年08月29日，星期三与Flex和Coldfusion案卷upload/下载component操作时我碰到一些好玩的Linux 和Windows file format的问题。Upload一些案卷没问题但是,除非你使用Coldfusion的缺省charset tag, Linux服务器的JPG就办不动。
XML:
-
<cffile action=“read”
-
file=“#folder#/#filename#” variable=“fileData”/>
-
<cfcatch>
-
<cflog text=“Invalid read #folder#/#filename#”>
-
</cfcatch>
-
</cftry>
-
<cfif fileData NEQ “”><cfcontent reset=“yes”><cfoutput>#fileData#</cfoutput></cfif>
用上述的代码会使JPG”不能用”。在tag内你必须设定”charset”。下面的代码有效。
XML:
-
<cffile action=“read” charset=“iso-8859-1″
-
file=“#folder#/#filename#” variable=“fileData”/>
-
<cfcatch>
-
<cflog text=“Invalid read #folder#/#filename#”>
-
</cfcatch>
-
</cftry>
-
<cfif fileData NEQ “”><cfcontent reset=“yes”><cfoutput>#fileData#</cfoutput></cfif>
依靠服务器的缺省, 我认为还有别的办法。所以当你想从Linux 服务器,用ColdFusion下载二进制案卷时,要小心charset的问题。
New Scorpio/CF8 Yahoo Pipe
2007年08月28日，星期二I am still wading through all of the new Scorpio/CF8 information. There is a lot of it and in order to get a better handle on all the topics and postings, I created a new Yahoo Pipe. The full URL is: http://pipes.yahoo.com/pipes/pipe.info?_id=4Awn2KYW3BG_hwQ5y6ky6g
I picked the top 8 blogs for Scorpio news based on an unscientific cruise through the ColdFusion Yahoo Pipe from Brian Rinaldi. There are blogs I have missed so if you know of a good resource for Scorpio/CF8 postings, please leave the feed URL in a comment.
I want to be strict on this one and keep a valuable signal to noise ratio. My own blog is not included in this feed yet, because I haven’t posted anything significant on Scorpio/CF8.
So let us make this Scorpio specific please. If you have a Scorpio feed, please submit that one rather than your generic feed URL.
Scorpio Beta works with Apache 2.2.4.
2007年08月28日，星期二Just a quick note to say I was able to get CF 8 working on a fresh install of Apache version 2.2.4.
A few quick observations:
- During the CF install, I got a nice message reminding me to stop ColdFusion MX 7 Search Server
- After CF finished, I had to run the Web Server Configuration Tool again for everything to work
- I also had to open Port 51800 on my local firewall. I suspect this is JNDI.
My initial impressions? The installation process handled the above issues gracefully. This doesn’t feel much like beta software… Remind me to tell you about an experience I had with a SQL Studio Express CTP beta once.
Editors note: I suppose I will never be hired by microsoft if I keep making these sorts of comments
Anatomy of an SQL Injection Attack
2007年08月28日，星期二Security is everyone’s problem. It is important to be aware of issues that can foster security violations in software. Buffer Overflows, a common software security hole, arise from the length of input not being checked. When the input is larger than the memory allocated, the input data can spill over into unintended memory addresses. By appending a command with the correct offset, it is quite possible to push the command into memory space with high level privileges and execute.
In a buffer overflow attack, often the application accepting the input is running under reduced privileges. Because the input overflows the given memory address, it matters not that the input originated from a low privilege application, but rather the actual memory address where the command is stored and executed.
SQL injection is another type of attack and shares a common root with Buffer Overflow attacks. When input is not properly evaluated and filtered, bad things can happen. In an SQL injection attack, the attacker appends SQL statements to input. Here is a simple query:
SELECT userID, username, password
FROM Users
WHERE UserID = 1
Here is an example of appending a command to an SQL statement.
SELECT userID, username, password
FROM Users
WHERE UserID = 1; DROP Users;
In the last example, an SQL command to drop the users table was added. Imagine for a moment the URL to access a user profile. http://someserver/index.cfm?userID=1 The userID is appended to the URL and is undoubtedly passed to a query in the application that returns the profile associated with UserID 1.
To create an SQL Injection attack with the URL above, we could simply try the following URL: http://someserver/index.cfm?userID=1;drop users When the application substitutes the userID value of 1;drop users in the query, there are actually two statements to be executed. Firstly, the command to return the data from the users table associated with userID 1. Secondly, the command to drop the whole users table.
Pragmatically speaking, there would be little to gain by dropping the users table apart from vandalism. That being said, there are thousands of 5kr1pt k1dd135 whom would be delighted in dropping your users table for you and then bragging to their little wanker friends about how they trashed your server. Still, not much of a security risk? Let us try another angle.
Suppose for a moment a site that charged a lot of money for access to data. Users periodically purchased subscriptions and your organization was making millions. Inside the database was a users table with the field of ‘ExpirationDate’ representing the date the subscription would need renewal. Shall we form an attack to give us a 20 year subscription?
This is the SQL we wish to execute
SELECT userID, username, password
FROM Users
WHERE UserID = 1; update Users set ExpirationDate = ‘5/5/2027′;
Can you guess what the URL string would look like?
If you guessed: http://someserver/index.cfm?userID=1;update users set expirationdate = ‘5/5/2027′
Then you are close. We may need to massage the url a little, or find a text input to put our command if the spaces and quotes are not respected.
So we can bump our subscription up 20 years or so. What else can we do? Let us try to add a user.
This is the SQL we wish to execute
SELECT userID, username, password
FROM Users
WHERE UserID = 1; insert into Users (username, password, expirationdate) values (‘imahaxor’,'inyourbox’,'5/5/2027′);
http://someserver/index.cfm?userID=1;insert into Users (username, password,expirationdate) values (‘imahaxor’,'inyourbox’,'5/5/2027′);
Now in place of just extending a subscription, the attacker has a new account that won’t expire for a while yet. Not a pretty picture is it?
How can you defend against SQL injection attacks, you ask? There are some best practices you may follow to reduce your attack surface. Let us look at a few:
- Reduce the privileges given to the SQL user of your application. If the application never needs to insert into a particular table, then remove that privilege.
- Check your input values. If you are expecting a number, add val() around the value. In the case above, a simple val() statement turns a malicious statement into a simple 0. We all like 0, right?
- Use prepared statements. Adding cfqueryparam values to your dynamic query values adds great protection. Apart from escaping malicious characters, the prepared statement treats the value as a value, not a string of text to be executed by the SQL engine.
While software is extremely difficult to secure completely you can remove a giant risk by gaining understanding of SQL Injection Attacks and using the techniques above.
Below are some examples of a recent attempt to use SQL Injection on my blog. This attack was not very sophisticated, but could have disrupted the services of this blog.
Invalid data FDEB2819-9F27-DDC8-3C7C7A4B29BC8149 and 1=2 value exceeds MAXLENGTH setting 35..
This was a fingerprinting attack. If the attacker retrieved a page, then the input mechanism would allow sanitized input. If the attacker received an error message, then perhaps important information about the server configuration would be revealed.
Invalid data FDEB2819-9F27-DDC8-3C7C7A4B29BC8149 and char(124)+user+char(124)=0 value exceeds MAXLENGTH setting 35..
This string evaluates to FDEB2819-9F27-DDC8-3C7C7A4B29BC8149 and |user|=0.
Invalid data FDEB2819-9F27-DDC8-3C7C7A4B29BC8149′ and char(124)+user+char(124)=0 and ‘%’=’ value exceeds MAXLENGTH setting 35..
This string evaluates to FDEB2819-9F27-DDC8-3C7C7A4B29BC8149 and |user|=0 and ‘%’=’
A tool to generate Database Scripts from MySql, MS SQL, Oracle, Pervasive and PostgreSQL
2007年08月28日，星期二While reading the latest on the very cool Trac-Fu project, by Russ Johnson, I was reminded about a tool I use to generate database scripts.
I am a fan of most all Database platforms. I have used MSSQL 7/2000/2005, UDB, Oracle 8/9i/10g, MySql 4/5 and PostgreSQL 7.3/8.1 in production. When working in multiple database platforms, there inevitably comes the need to port a database from one platform to another.
Rather than spend my days mapping and transforming a database schema, I use SQL Script Builder. SQL Script Builder is a free tool that generates SQL scripts of a database and the data for 5 officially supported platforms:
- MySql
- MS SQL
- Oracle
- Pervasive
- PostgreSQL
I have also used SQL Script Builder to port a non-trivial MS Access database to PostgreSQL, a job that would normally be very manual and annoying.
This tool will produce SQL Scripts that can be executed on a local or remote server. Very handy indeed when working with remote hosts.
The support is also very good. I personally asked for PostgreSQL support on February 24 2007. Support for the PostgreSQL database was added and the documentation updated on March 06 2007.
Depending on your database structure, there are some tasks you will have to do manually such as column constraints and Foreign Key relationships, but this is a small price to pay for the level of automation provided by SQL Script Builder.
A big Thank You goes to Dave and the rest of the team at SQL Script Builder for providing and supporting this great tool.
To read more about SQL Script Builder:
Update:
Dave wrote in to say that SQL Script Builder now has support for values that are NULL contained in INSERT statements. Thanks Dave!
ColdFusion Ajax Wizards for Eclipse?
2007年08月28日，星期二Sometimes I read too fast. I remember reading a post on Scorpio by Sean Corfield. I just don’t remember the part about the ColdFusion Ajax Wizards for Eclipse.
Here is the section of the post that caught my eye on the second reading:
Next up was what is proving to be my favorite: AJAX integration. Ben said that Scorpio’s AJAX features fall broadly into three categories: making CFC invocation easy, controls & widgets, application wizards. The latter is an AJAX version of the Flex “super wizard” for Eclipse that can create a full AJAX data admin application directly from your RDS data source. Very impressive but, as with the Flex wizard, Windows only because it relies on the same visual query builder (that folks know and love from HomeSite).
I’ve used the Flex Wizards before. The wizards are very easy to use and will create non-trivial applications, from the server-side. Since they are the same wizards, I imagine throwing together an Ajax enabled application is going to be a piece of cake.
Apart from the mention on Sean Corfield’s blog, I haven’t seen or read any other news about this feature. If anyone has a link or some information, please post a comment.
OO and Lines Of Code
2007年08月28日，星期二I’ve seen more than once, a person asking on a mailing list about a more ‘OO’ way to solve a problem. Often, this comes with the expectation of less lines of code to write. Certainly, as programmers paid to provide solutions, not lines of code, we want to deliver a tight and concise code base, however, OO is not about reducing the lines of code it takes to solve a problem.
Encapsulating a problem requires MORE code initially. Accounting for variability, for future extensibility and for the sake of clean architecture, it might take double or triple the lines of code than a procedural solution.
The benefits of OO come down to extensibility and to maintainability. Having a specific solution stuck neatly and tidily into a corner of your application will make alterations and enhancements easier. Often, the “OOOPs, I missed that one” problem is solved because there is less, or no code duplication.
Certainly, the more a particular encapsulated solution is used in an application, it will result in a reduction in overall lines of code. You won’t see this benefit when you write the solution the first time. You might even ask yourself, “Why did I write all that code, just to do ‘x’ ”
I’ve recently picked up some consulting work on a procedural application running CF 4.51. I spent a few years working on that particular version of ColdFusion and wrote some good software. CF 4.51 has no components, no UDFs, no StructAppend and no CFDump. If I occasionally get frustrated with OO, spending an hour or two with the 4.51 based code and it’s highly procedural architecture, prove that OO is worth the time. The time it takes to invest in yourself,to learn OO and in extra time to initially code the application.
So as you explore and learn Object Oriented programming in ColdFusion, be careful of how you set your expectations. OO can be quite frustrating and annoying at times. The benefits might not be what you initially expect. The rewards come later.
Passing querytext as a function argument OR When is a string not a string?
2007年08月28日，星期二Today I wrote a function that added in default rows to a query. Part of the function needed the text of a query to find the default values. I attempted to pass in the query text and use the same text in a <cfquery> tag.
This did not work.
Here is the query I passed in:
SELECT This, That FROM Those WHERE bar = ‘foo’ ORDER BY SortOrder
Here is the resulting error:
Error: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ‘Foo’.
After a few minutes, I figured out the query worked fine if I left the criteria out. This worked:
SELECT This, That FROM Those ORDER BY SortOrder
Well, it kinda worked. It parsed and executed fine but I obviously got more records than I wanted. I expected it was a wierd parsing error and then used chr() function to replace the single quotes AND the equal sign. Nada.
The final product that worked with the criteria was to use toString(). <cfsavecontent> would probably work also.
<cfquery name=“ColumnQuery” datasource=“#DSN#”>
#toString( arguments.SQLForRowTypes)#
</cfquery>
It was not obvious this wouldn’t work without the toString(). I could dump the arguments scope and see the query text VERBATIM. I could also copy the cfdumped text and paste it directly in QueryAnalyzer and it also ran. I am still scratching my head over this one.