Using MySql as an ASP.Net Membership Provider

There are a number of articles on this subject, but they all seem to be a bit out of date and do not reflect the current 'state of play'.

Background

I have recently been delving into ASP.Net, in a bit more detail than previously. I have, for a little while, been wanting to understand how the integrated log-in security worked and found this great walk though from Microsoft. This walk through uses MS SQL Server and I wanted to try using MySQL as well.

The main reason I wanted to try this is that one of my colleagues is shortly starting a project using ASP.Net and MySQL and I thought it would be useful to be able to give some useful advice on how to handle role management in this scenario. I have to admit to some personal curiosity as well :-)

The Process

For my first attempt at this I found this tutorial on the MySQL website. Before following the tutorial I downloaded and installed the latest version of MySQL Connector/Net (version 6.7.4 at the time of writing).

The information in the tutorial pertaining to connection strings appears to be incorrect and I was unable to get a connection to the database. A Google search led me to this site giving examples of valid connection strings. I was able to get a connection using the following:

<add name="LocalMySqlServer"
   connectionString="Server=<server>;Port=3306;Database=test;Uid=<user name>;Pwd=<password>"
   providerName="MySql.Data.MySqlClient"/>

I was then able to run the ASP.Net Configuration and get the databases created. However, every time I tried to add a new user the system crashed with an exception message that suggested that the MySql.Web assembly could not be loaded.

I eventually realised that the MySQL Connector/Net assemblies were not in the GAC. I tried starting the Visual Studio Command Line and using gacutil.exe to add the assemblies to the GAC, but although it said that the actions had been performed correctly the assemblies did not show up when looking in the GAC (C:\Windows\assembly). I then tried dragging and dropping the assemblies into the GAC, but this did not work either.

Another tutorial that I came across suggested that the installation should have put the assemblies into the GAC automatically, but this referred to an older version of the connector. This led me to the idea that maybe an older version of the connector would install correctly and save me some grief.

I then uninstalled the connector and  downloaded and installed version 6.6.5 (the last available version before 6.7.4). After tweaking the config settings slightly I was finally able to get the Create User function to work in the ASP.Net Configuration Tool so that I could create a user.

Tutorial Modification

When following the MySQL tutorial referenced above, the following things need to be borne in mind:

• When installing the MySQL Connector/Net, make sure that the assemblies have been installed into the GAC. If this is not the case, try installing an older version.
• Section 3 of the tutorial can be ignored. With the assemblies in the GAC they don't need to be explicitly referenced in your project.
• Modifying machine.config is unnecessary. Instead, add the connection strings and membership information to your applications web.config file. I have included templates for the relevant sections below, along with some information on how to obtain information for sections you need to fill in yourself.

Hopefully that should be enough information to get you on your way.

web.config

Add a <connectionStrings> section if necessary and then add the following entries:

<remove name="LocalMySqlServer"/>
<add name="LocalMySqlServer" connectionString="[1]" providerName="MySql.Data.MySqlClient"/>

[1] You can use the connection strings site referenced above to help work out your connection string if you are not sure what to fill in here.

Add the following section to the root configuration section:

<membership
   defaultProvider="MySQLMembershipProvider"
   userIsOnlineTimewindow="15">
   <providers>
      <clear/>
      <add
         name="MySQLMembershipProvider"
         type="[2]"
         connectionStringName="LocalMySqlServer"
         applicationName="MyApplication"
         enablePasswordRetrieval="false"
         enablePasswordReset="true"
         requiresQuestionAndAnswer="true"
         requiresUniqueEmail="true"
         passwordFormat="Hashed"
         autogenerateschema="true"/>
   </providers>
</membership>

[2] You can get the type from machine.config which is located in the Config folder in the appropriate Microsoft.NET/Framework/v[version number] folder. Open the machine.config file in a text editor and do a search for membership. When you find the appropriate membership/providers section, copy the type string from the MySQLMembershiProvider.

For reference, the type string I am using is:

type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.6.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"

How to use the Oracle Imaging API Search Service with .Net

Introduction

I recently had to work out how to implement the Oracle Imaging API Search Service in Microsoft .Net, so that I could use a saved search. This proved to be a challenge on a number of fronts, so I have decided to record the process here.

Importing the Service

Importing the service was the easy part. As it is a standard web service I just had to import the WSDL. Importing the Search Service requires the following URL:

http://[server address]/imaging/ws/SearchService?WSDL

In Visual Studio you need to add a service reference, which will give you the following dialog.

Add Service Reference

Put the URL into the address and press "Go". Once the WSDL has been imported you can choose a namespace and press OK. I used "Oracle.Imaging" as the namespace.

Security and Configuration

The Oracle API requires a username and password to be supplied. Working out how to do this, however, is no easy task. Like many things though, when you know how it turns out to be easy.

The App.config file contains configuration for the web service. the element contains an child element with a number of attributes. You need to add extra elements under then endpoint so it looks something like the following:

Security Configuration

Depending on how much data you expect to receive back, you may need to increase the buffer size as well. To do this, you will need to add some extra attributes to  the binding. Where you have you will need to add the attributes maxBufferSize and maxReceivedMessageSize. The values for the attributes should be identical and will be a number of bytes.

Using the Service

To actually use the service you need to instantiate a SearchServiceClient instance. You can then use the executeSavedSearch method to actually invoke a saved search on the server. The arguments to this method are a NameId instance and an array of SearchParameter.

Creating the NameId is straightforward, but there are a few gotchas that one needs to be aware of when creating the search parameters.

The general process involves creating a SearchParameter, a SearchValue, and a TypedValue. The TypedValue will be assigned to the typedValue property of the SearchValue and the SearchValue will be assigned to the parameterValue property of the SearchParameter.

for the saved search, all value types must be set to TEXT and it is very important that the typeSpecified property of the TypedValue is set to true. If it is not then an important attribute will not be included in the SOAP request and incorrect results will be returned.

The following is an example of creating a search argument for a "SUPPLIERID" parameter.

             searchArguments[0] = new SearchParameter();
             value = new TypedValue();
             value.type = FieldType.TEXT;
             value.Value = supplierId;
             value.typeSpecified = true;
             searchValue = new SearchValue();
             searchValue.searchValueType = SearchValueType.TEXT;
             searchValue.typedValue = value;
             searchValue.searchValueTypeSpecified = true;

             searchArguments[0].parameterName = "SUPPLIERID";
             searchArguments[0].parameterValue = searchValue;
             searchArguments[0].operatorValue = @operator.EQUAL;
             searchArguments[0].operatorValueSpecified = true;

And that should be all you need to know to successfully consume the Oracle Imaging API from your .Net application.

Implementing WS-Security when consuming Web Services from .Net

This article is a mine of information, not only on this subject, but also on calling the Oracle WebLogic Server web service using .Net.

Using Express with node.js

Hacksparrow has a good tutorial for getting started with the Express framework. But for brevity, I will explain the basic instructions for getting started.

First you need to install express.js as a global module

npm install express -g

You then need to create a directory and create an Express app inside it.

md <dir name>
cd <dir name>
express --sessions --css
npm install

That will give you the most basic application template with session support and normal CSS. You can use stylus CSS format with the command "-css stylus" and less with "-css less".

Continuing VB.Net statements over multiple lines

In older versions of VB.Net (prior to VB2010 I think), you were required to use the line continuation character (the underscore "_") whenever you split a statement over multiple lines.

From VB2010 you can omit this character in a large number of cases.

For a complete list of cases where you can omit this (annoying) character, search the help for "statements in visual basic" and then scroll down to the section entitled "Continuing a Statement over Multiple Lines". This contains a sub-section entitled "Implicit Line Continuation" which lists "the syntax elements that implicitly continue the statement on the next line of code."

JSON and SQL

I have a project where, for auditing purposes, I serialise a JavaScript object to JSON and then store the resulting string in a MySQL database table.

Recently I encountered a problem with de-serialising some of these strings back to objects. The problem was that a string field in one of the instances contained embedded quotes and these were not escaped in the SQL table. After a little while spent digging about I realised what the problem was.

When JSON.stringify is called on a string, any embedded double-quote characters are escaped, so the string 'This contains "double quoted" text' becomes 'This contains \"double quoted\" text'. This is all fine, but when you add a string to the database the escape character is treated as an escape and is removed from the resulting string. If you then read the string back out and try to JSON.parse it the un-escaped double-quotes cause it to fail. To keep the escape character you need to escape the escapes before adding to the database.

You also need to escape any single quote characters before adding to a database table.

Try:
 JSON.stringify(obj).replace(/'/g, "\'").replace(/\\/g, "\\\\");

More about QueryString in ASP using JavaScript

The Request.QueryString(name) method returns a VB Collection object. This link defines the properties and methods of the Collection object. The main ones we are concerned with are Count and Item.

The Count property (as mentioned in my previous article) gives a count of the number of items it the collection and the Item property allows you to get at the individual items. Two thing should be noted about the Item property: first that it is a method not an array and second that it is one based not zero based, so the first item is retrieved using Item(1) not Item[0].

For example, if your QueryString contains "widget=abc&widget=xyz" and you do:

var widget = Response.QueryString("widget");

widget.Count //  2
widget.Item(1) // "abc"
widget.Item(2) // "xyz"