In my previous post I explained cbauth based authentication combined with annotation based security. Annotations are easy to understand, so good as a starting point, but if you need something more flexible you need security rules. So when would you need security rules?
Continue readingCategory: Coldbox (Page 3 of 3)
Adobe introduced full null support in ColdFusion 2018. Before 2018 null values returned by databases and external systems were converted to empty strings. This behaviour made it almost impossible to distinguish between empty strings and null values, and also caused issues with serialization and deserialization. You also had to take special precautions to insert or update null values in a database. Interaction with Java, including CFORM based hibernate could also result in returned null values so we always had te be very careful when trying to handle these dreaded nulls.
Lucee already has full null support since many years, but just as with the latest ACF version, you have to enable this in the administrator, just to stay compatible with older versions of ACF.
So, because of all these null handling issues, why not enable full null support? That’s what we imagined when creating our latest REST based application. But before hurrying to your admin panel better think if it will break something.
Before the Modernize or die times as promoted by Ortus Solutions there were many ways to find out in Coldbox if all input variables existed. Let me give these two JSON examples, both are used for the creation of a user in the body of a post request:
//first example, customer_id as a null value because customer is not selected yet
{
"customer_id": null,
"username": "wdb",
"password": "topsecret"
}
//second example, customer_id omitted because customer is not selected yet
{
"username": "wdb",
"password": "topsecret"
}
When we post this userdata, I want to validate if my customer_id is there so I can populate some required customer field. When null support is not enabled we have a few options to check if we have a usable customer_id.
function createUser( event, rc, prc ) {
//option 1
if ( event.valueExists("customer_id") ){
//....
}
//option 2
if ( structKeyExists(rc,"customer_id") ){
//....
}
//option 3
if ( !isNull(rc.customer_id) ){
//....
}
The first options seems to make sense. Event.valueExists is a way to check if my customer_id is a value in my request collection.
The second options is slightly more low level, it just checks the request collection for the existence of customer_id.
The third option is a little bit more direct. It just checks if rc.customer_id is not null so I can continue.
So it seems it doesn’t matter which method I use. But now I enable full null support, and the whole landscape is changing. In the previous part I just assumed the key should be there, or not be null.
But now I have to know what my frontEnd VueJS developer is doing. Is he sending a null value for customer_id if there is no customer selection yet? Or is he only sending values in the JSON body which already have a value? This will make a lot of difference .
If my frondend developer is sending null values and I enable null support in CFML my event.valueExists and StructKeyExists() checks are completely useless, because my customer_id variable now does exist in the request collection. The problem here is it’s value is useless, because I can’t retrieve a required customer based on a null value. So the only safe check now is the !Isnull(rc.customer_id) .
The situation is still a bit different if you also want to be able to update a database based on the input of a null value. In that case you have to check for both Event.ValueExists AND IsNull.
The above examples seem quite trivial, but a word of warning is appropriate here. You really have to know how your frontend application should handle these null values. Are you validating ALL properties, or only the properties which have been entered by the user?
A second important point is the handling of nulls in some of your supporting libraries. We failed a lot of tests because one of our libraries tried to determine the datatype of our input automatically. Since null values have no datatype the library failed. This was fixed very soon, but especially when using some older libraries or modules you really should take a close look at the code when possible.
I can highly recommend the qb library for all kind of database manipulations, for querying and data definitions. Qb hides a lot of complexities for database actions, and abstracts away differences between database engines.
Although very powerful, some options are really well hidden in the documentation or the source code. Recently I wanted to update a database table with null values. The normal syntax for updating values in a database table is:
query.from( "users" )
.update( {
"email" = "foo",
"name" = "bar"
} );
If you have more complex requirements like inserting date values in the correct formats, you can specify query param options, e.g
query.from( "users" )
.update( {
"email" = "foo",
"name" = "bar",
"updatedDate" = { value = now(), cfsqltype = "CF_SQL_TIMESTAMP" }
} );
This query param syntax comes in handy when inserting NULL values. As we all know older versions of Adobe Coldfusion and Lucee can’t handle null values very well:
query.from("user")
.whereId( 10 )
.update( {
manager_FK = { value = "", null=true },
} )
If you are using Lucee with full Null support you can leave out the null parameter, e.g
query.from("user")
.whereId( 10 )
.update( {
manager_FK = { value = null },
} )
Cfmigrations is a nice tool to describe database changes and version them with your application code. It comes in two flavours as a forgebox package: cfmigrations if you want to manage your database changes from within you application and commandbox-migrations if you want to do the same from within commandbox. Actually the commandbox version is just a wrapper around cfmigrations. The forgebox description gives a nice overview how to use this tool.
How to use: some MSSQL and java problems
So why this post, if the manual is already there? Because it doesn’t always work out of the box, and you have to know how to use this tool in commandbox. So let’s start.
First we are going to install commandbox-migrations. Start up commandbox in your homedir and execute
install commandbox-migrations
If you want to start using migrations, you have to initialize it. This will not do any database activity yet, it just creates the right keys in your box.json.
migrate init
The following entry will be created in your box.json
"cfmigrations":{
"schema":"${DB_SCHEMA}",
"connectionInfo":{
"password":"${DB_PASSWORD}",
"connectionString":"${DB_CONNECTIONSTRING}",
"class":"${DB_CLASS}",
"username":"${DB_USER}"
},
"defaultGrammar":"AutoDiscover"
}
Save yourself some trouble and change your default grammar immediately, because the Autodiscover probably doesn’t work within commandbox. Your options (as documented here) are: MSSQLGrammar, MySQLGrammar, OracleGrammar and PostgresGrammar. We choose MSSQLGrammar. As you can see from the box.json, most options are populated with environmental variables instead of real values (the values surrounded by ${} ). This is a good practice so your credentials don’t end up in you code, and you probably need different values for development and production. To use environmental variables in your commandbox environment you can install the commandbox-dotenv package. Now you can create your environmental variables in a file called .env in your root directory. Please make sure you exclude this file from version control!!!
Now you need to set your environmental variables DB_PASSWORD, DB_USER, DB_SCHEMA, DB_CONNECTIONSTRING and DB_CLASS. Values for password and user are obvious, but what about the other values? In my environment I can leave the DB_SCHEMA empty, but I need values for DB_CONNECTIONSTRING and DB_CLASS. If you don’t know these values yet, you can find these values by creating a a datasource in your Lucee Admin. If you edit this datasource, you will find some useful info at the bottom of this screen, like this
So I entered all values for the Microsoft driver in my .env file and tried to install the tables necessary for cfmigrations (by default called cfmigrations. I executed this:
migrate install
This will NOT work. You have to reload commandbox first, to retrieve your added (or changed) environmemtal variables. You can do this just by executing the command reload. If you forget to reload, you will probably get a java.lang.NullPointerException. But after reloading and trying to create a valid MSSQL connection we will enter Microsoft/Java problem territory
The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “java.security.ProviderException: java.security.KeyException
For some reason the Microsoft driver is not very cooperative in a commandbox environment, although I have no issues when used in my Lucee server. The commandbox gitbook manual has some info on hitting databases from commandbox: https://commandbox.ortusbooks.com/task-runners/hitting-your-database and indeed:
Microsoft SQL Server Issues
If you are getting SSL related exceptions when trying connecting to an MS SQL database using the Microsoft SQL Server (JDBC4) driver (com.microsoft.sqlserver.jdbc.SQLServerDriver), try using the jTDS driver (net.sourceforge.jtds.jdbc.Driver) instead.
So, that’s really a waste of time if you try to use cfmigrations with SQLServer for the first time! Actually I ‘ve used it a zillion times with older versions of commandbox back in the Lucee 4.5 days without any problems. But hey, problem solved! I changed my settings in .env and reloaded commandbox
If I execute migrate install again, cfmigrations is ready for some real database modifications, which I will describe in a later post.
Cfmigrations really is a very handy tool. It is nice if you can reconfigure your database tables directly from commandbox, but you really need some extra info in advance, such as:
- how to work with commandbox-dotenv
- why is a SQL driver misbehaving in a commandbox environment, and more important: how to avoid this by using the jTDS driver.
I am sure the process is a lot easier with some other databases, but I hope this write-up helps. If you really want to avoid all the trouble in configuring your database, you can also use cfmigrations from within your application, where you have your datasource already available.
When developing a ColdBox REST API we wanted to add multilingual capacities. Coldbox has a nice module cbi18n for this. When you set a new locale using
i18n.setfwLocale(newLocale);
this locale will usually be saved in a cookie and sent back with every request. But for our REST API we are looking for a more standard way of content-negotiation. We want to set an Accept-language header on every request and return a Content-Language header for one of our supported languages. The Accept-language can contain multiple choices, and each choice can have language, country and a q factor which specifies a preference. Example:
Accept-Language: fr-CH, fr;q=0.9, en;q=0.8, de;q=0.7, *;q=0.5
In our case we are only serving two languages, English (en) and Dutch (nl) which is the default if no header is specified. So, in our header we need to find out which language (nl, en or something else) is requested and if it is not Dutch (nl) we serve English. In a later stage we might add German for some customers but for now these two languages are sufficient.
Coldbox interceptors are the perfect vehicle for this kind of logic. We define a LanguageInterceptor which has the following code:
// Language interceptor
component{
property name = "i18n" inject="i18n@cbi18n";
void function configure(){
}
function preProcess( event, interceptData, buffer, rc, prc ) {
var languageHeader =event.getHTTPHeader( "Accept-Language", "nl" );
// get first language from comma separated list
//cleanup for potential ;q weighting factor
//plus cleanup for potential - dash separator
var firstLanguage = listFirst(listFirst(listFirst(languageHeader),";"),"-");
var newLocale = ( firstLanguage == "nl") ? "nl_NL" : "en_US";
var contentLanguage = ( firstLanguage == 'nl') ? "nl-NL" : "en-US";
i18n.setfwLocale(newLocale);
event.setHTTPHeader( name = "Content-Language", value = contentLanguage );
}
}
As you can see from the listfirst/listfirst/listfirst line, there is some parsing involved, because there might be multiple language entries separated by a comma. Each of these entries could have a q factor appended (after a ; character) and finally there might be a country designation after a –. So finally we end up with nl, en or something else. Coldfusion locales are formattet as nl_NL or en_US for example, and in the Content Language returned everything should be formatted with a dash instead of an underscore.
Finally, don’t forget to register your interceptor in Coldbox.cfc or your ModuleConfig.cfc!
Recent Comments