Category Archives: SQL

Topics which relate to SQL interaction and development, specifically from SQL 2000, 2003, 2005, 2008 and up.

.NET Applications on Linux

Well, what a touchy subject this might be to some people.  I have always seen the battle go back and forth between Windows, Macintosh and Linux.  Windows being a middle-tier price range which excellent performance, Macintosh being the high end simply from marketing and Linux being the low end cost point which the most potential.  The problem I have always seen is that Microsoft holds the middle share which is always the most used share.   I have been a Microsoft developer for my entire career and I love it to death, but the power behind a Linux machine is starting to become hard to ignore.  Recently I ran into a project that was faced with spending 100 hours developing a communication platform for a piece of software or somehow getting .NET hooked into a Linux server.

Of course we went both routes as with any project whichever option is the best is the choice, but something has to work.  I came across this plugin for Apache and Linux called Mono.  Mono is a plugin/server application that lets you run ASP.NET applications on your Linux server native.  You do not have to get pushed to another server or lose your performance, you simply install the package and configure it in Apache and you are up and running.  To start, here is the mono website for you to check out and hopefully spread out through all of your Linux servers: http://www.mono-project.com/Main_Page

Hopefully you all install it and configure it so when I come through I can install my applications on your server and be just fine and dandy, if you don’t I will no doubt make you do it :)  There is a set of install instructions for each server type including Mac servers as well as CentOS, Ubuntu, Debian, etc.  Mostly it seems to just be a package installation through w-get or whatever your package flavor is.  After configuration you can use this site to configure virtual directories: http://go-mono.com/config-mod-mono/Default.aspx

The nice thing about this configuration generator is that you can create a separate config file for each virtual directory and include them in your base httpd config file under each virtual host settings section.  It is almost exactly like creating virtual applications in IIS and the performance is for the most part the same.  From what I have seen this far, .NET 4.0 is supported as well as AJAX, and I am hoping to see some more stuff go into the project to make it a viable option for hosting .NET applications.  Cheers!

How to find your GP Company ID

Often times when working with Dynamics GP you will come across something that is asking for a GP company ID.  This can either be an integration application, or a registration program for a cool addin, or a number of other pieces that need to connect to your data.  GP has three ways to identify a company: Company Name, Database Name and Company ID.  All of these luckily can be found in one table in a database.  By selecting out of the SY01500 table we can determine how to find your GP company ID.  Here is an example query:

How to find your GP Company ID

SELECT CMPNYNAM, INTERID, CMPANYID
FROM DYNAMICS.dbo.SY01500

how to find your gp company id

This will return back the Company Name as CMPNYNAM, the Database name as INTERID and the Company ID as CMPANYID.  The company ID will always be an integer, and the test company is usually -1.  The InterID is always a three letter word and potentially a number.  Example: DYN01, DYN02, etc.  The Company Name will be the value that you fill out during setup.  These tables are how to find your GP company ID and they will provide additional information about your company if you want to look further by selecting all columns.

While looking for how to find your GP company ID may seem somewhat difficult in GP it is typically best to look directly at SQL.  GP does not have a great method for looking up company information and you will likely still be asking yourself how to find your GP company ID.  Again, because this information is typically only required for an integraiton or special third party addins it is unlikely that GP will show you how to find your GP company ID anytime soon.

If you are looking for more information on how to find your GP company ID

Check out this excellent SQL posting from Victoria Yudin about how to find your GP company ID in SQL.

Prevent SQL Injection Attack With .NET

There has been a large stir recently with how to prevent SQL injection attacks with .NET.  Huge rumors are flying about viruses because of the nuclear incident that happened in Iran.  Just to be clear, regardless of how secure any site is there is a likelihood that you can be hacked.  The best we can do is prevent as much as possible so that it takes someone who is really good to do it.  At that point you are dealing with a security expert and likely they will just want you to pay them for the information.  Back on to the subject, to solve our security problem we must first ask ourselves, what is a SQL injection?  SQL injections are a trick that hackers use to execute malicious SQL scripts on your server.

Our main goal is not to take away functionality, but to prevent SQL injection attacks

Let’s say you have a login form, and you ask for a username and password.  You have a text box bound to both fields and when they hit a Login button your form code selects from the user table where user name is equal to the User text box.  The SQL might look something like this:

SELECT UserId, UserName,Password FROM Users
WHERE UserName = '" + txtUserName.Text + "'

prevent sql injection attacks
This is where a hacker can come in and where we need to prevent SQL injection attacks.  The user name a hacker would fill out would be something like this:

'; DROP DATABASE myWebApp --

When your code executes it will send a drop database command to your server destroying all of your data.  This is likely not the intent of the hacker as they would much rather send a command to validate their password or fetch data but the goal here is to prevent SQL injection.  Here are the three big steps to prevent SQL injection attacks:

Three methods to prevent SQL injection attacks with .NET

Validate your data

The first step in a SQL injection attack is to know what the developer is expecting to happen with a field and exploit it.  In reverse, the first step to prevent SQL injection attacks is to know what a hacker intends to do with a field and prevent it.  This will involve checking that your text received is the right length, scrubbing for invalid characters and make a decent attempt at stripping out dangerous SQL commands or throwing back errors if you find anything.

Use SQL Stored Procedures

Stored procedures are the next great .NET tool, because a parameter passed through a stored procedure command is sent as literal text as opposed to executed with a command.  While converting your commands to a stored procedure does not prevent SQL injection attacks it does give you an additional security layer in case the injection makes it through and is targeting specific commands.

Use Parameters with Dynamic SQL

Another way to prevent SQL injection attacks is to embed your input from forms as parameters as opposed to injecting them directly into the statement.  This can be done simply by using an @ sign as a parameter in your statement and appending a parameter to your command object.  This looks something like this:

SqlDataAdapter saoSqlAdapter = new SqlDataAdapter(
         "SELECT UserName, UserId, Password FROM Users WHERE UserName = @userName",
         connection);
  myCommand.SelectCommand.Parameters.Add("@userName", SqlDbType.VarChar, 50);
  myCommand.SelectCommand.Parameters["@userName"].Value = txtUserName.Text;
  myDataAdapter.Fill(userDataset);

Other methods to prevent SQL injection attacks

After working these three methods you should be able to prevent SQL injection attacks from mostly all attackers, if you need more information on how to prevent SQL injection attacks check out how to prevent sql injection attacks on MSN.

Primary Keys in SQL (Guid or Integer?)

Primary keys in SQL have come up quite a few times in my career so far. Let’s start by going back to the beginning with what a key really is and how it is used.

Each concept in programming typically starts with a record or a target. In the case of bank applications this would be an account, or sales would be a customer or an order. Regardless there is always some sort of unique object in coding that needs to be identified, tinkered with and some task completed. For a customer you would call them by their full name, Mike Calvert as an example. Unfortunately there are a lot of people named Mike Calvert in the world, so we need to learn to identify the specific Mike Calvert we are talking to.

From here a key was identified by using some sort of numbering scheme. Dynamics GP uses a limited alphanumeric scheme which you can use to assign a number such as MIKECALVERT01. This works until you run out of letters or you hit the maximum combinations and start coming up with one offs. The next step in the equation was to jump to auto incrementing integers, so Mike Calvert becomes 1, Mike Calvert 2 becomes 2, Jason Mills becomes 3, Howard Roberts becomes 4 etc. Everything was solved in the world because we now had an endless number that works forever. Moving into actual SQL syntax, you would have an issue with connecting up data links. Lets say you had to insert 50,000 records into this customer table, and you had a relationship table that referenced these people.

You now have to insert 50,000 records into the table, and then determine which of the 50,000 got assigned to a customer record because that record ID is generated when you insert it into the database. There are some tips and tricks around it but it is very messy. In a nut shell, if I wanted to find out what the ID was of Mike Calvert after I inserted 50,000 people into the database I would have to do a select to find Mike Calvert, and we run into the same issue we ran into before where there may be more than one Mike Calvert. At this point someone at Microsoft (thankfully) decided to put a Guid into play. Guid’s are by far the best key choice that I have come across as they provide three awesome things that keys need. The first and biggest requirement is being unique. Each Guid is unique no matter what table or database. If I put a customer record into a table with a Guid, I know that I can find that same customer record using that Guid from anywhere, without having to worry if that Guid will show up anywhere. The next big advantage is indexing. Indexing a guid is very efficient as they are the same length of characters, the same types of digits and are always unique. Finally, Guids can be generated prior to insert.

Generating a key prior to insert is a major advantage, as you can wrap up your 50,000 inserts into a single statement as well as the relationship. .NET provides a function called System.Guid.NewGuid that returns back a guid that you can use as a unique identifier in your inserts. Let us say that we generated our customer Mike Calvert and we created his guid. Any additional tables that required a relationship to Mike Calvert would already have this guid prior to inserting Mike Calvert, so I can create Mike Calvert and his sales histories and payment histories and insert it in one transaction instead of making multiple calls to fetch IDs. Fun stuff eh?