SQL, strcat(), and more segmentation fault

Also known as: how to screw up a working program with strcat().

I've been working on learning to use Microsoft SQL. The trending/archiving system at my job uses SQL to record machine data (HVAC) so that we can prepare reports for management and our accrediting surveyors. Learning SQl has been helpful in helping me learn other programming languages because working toward a goal (making my SQL work without opening SQL) seems to motivate me to practice coding beyond just working on a tutorial.

Most of what I've done lately is to find something to do in SQL and then work through how to do that in Python, perl, nim, and C. Yes, I saved C for last.

Connecting with SQL databases and engines in the other languages was fairly straight forward, and not too difficult. However, when I got to C, things changed drastically. The only complete description of interfacing UnixODBC (what is needed to connect to SQL) with C is from a website called easySoft.com. I'll put a link below if you are interested.

This site, while complete with all one needs to use C in order to connect to SQL, left a lot to be desired in clarity. However, after a couple of days and an evening of frustration, I finally got their basic connection program to work.

While I was happy to get that far, there was a lot more to do. The first thing I needed to expand upon was a way to get connection information from the command line, format it correctly, and shove all that into the SQL connection function. Shouldn't be too difficult, right? I know C, right?

Wrong.

I knew the tool I needed was something to concatenate argument strings into the complete connection string. In Python, we just use '+' as in str1 + str2. No problem. In perl, we have dot notation as in str1 . str2. No biggy. Nim? Simple, use str1 & str2. C? Well....

C doesn't have a concatenation operator. But, it does have a handy-dandy group of string utilities available when one puts this at the top of their program: #include <string.h>.

This allows the programmer to use strcat(), a function that appends a source string (it is supposed to be a constant, but that seems fluid) to a destination string pointer then returns the new string to a result string pointer. And, there goes the neighborhood.

Each of the items in the strcat() statement have to abide by typing rules (which I am not complaining about, just pointing out it is difficult to remember them all). C is considered strongly typed and gcc enjoys reminding the programmer at every opportunity.

So the correct format to make strcat() work is:

*result=strcat( destination, source);

Where result and destination are definitely pointers; source should be a constant char array, but I have used a char variable.

As part of my frustration, I'm also trying to do this in the middle of a finicky SQL routine. I made progress after I got out of the SQL routine and just ran several mini-programs with just strcat() working in order to remove extraneous errors from the compiler.

So, to set up the final script, I had to create an exact copy of the following string that would go into my SQL connect function:

"DSN=mydsn_name;UID=sa;PWD=password"

Unless your really interested in going down the rabbit hole of SQL, just note that each of these has meaning to the connection function. A DSN is a Data Source Name; a specification of how to connect to an SQL instance (engine I call them). UID is a user name; sa being the system administrator (usually this isn't used, but I'm just testing). PWD is the correct password to go with the username.

What I want to do is run it like this:

$>sqlconnect mydsn_name sa password

And, hopefully, ODBC will respond with a nice little connect as my reward. However, to get there, this is how it looks:

char *dsn=malloc( sizeof(char) * 100 );
char dsnstring[100]="DSN=";
char uidstring[100]="UID=";
char pwdstring[100]="PWD=";
dsn=strcat(dsnstring,argv[1]);
dsn=strcat(dsn,";");
dsn=strcat(dsn,uidstring);
dsn=strcat(dsn,argv[2]);
dsn=strcat(dsn,";");
dsn=strcat(dsn,pwdstring);
dsn=strcat(dsn,argv[3]);

The argv[x] are the command line argument strings. You get them when you open your main() function as follows (and, yes, I screw it up often):

int main(int argc, char *argv[])

argc is an int(eger) variable that will hold the number of command line arguments. *argv[] is a pointer to a number of arrays of char(acter) type. argv[0] is the filename that you used to start the command with (in our case argv[0]="sqlconnect". Therefore, argv[1] gets the DSN we entered, and you go on from there.

I used malloc() to initialize my main variable (dsn) to a 100 character array, but empty. Technically, I reserved a memory space assigned to the pointer dsn. #include <stdlib.h> is needed to make it work.

dsnstring, uidstring, and pwdstring CANNOT be pointers to strings in the usual sense. I know--a character array is theoretically the same, but gcc does know the difference. So, yes, *dsnstring is out. I believe it has to do with the destination and result need to have room for appending characters and be the same length, but I haven't tested it; yet.

Strcat() will quietly not tell you anything (except segmentation fault) if your destination pointer has no room.

dsn=strcat(dsnstring,argv[1]) works like this:

Take a copy of what the dsnstring is pointing to and append the contents of argv[1] to that, then return the new string to the what dsn is pointing at (the 100 char memory allocation). At least, that is what I've deduced it does. Wash, rinse, and repeat with the next string needing to be appended.

dsn=strcat(dsn,";") looks like it should fail, correct? It doesn't. Because strcat() takes a copy of dsn, adds the semicolon and then RETURNS that to the original location, this construct is legal (and tested, but not thoroughly). Doing it like this, definitely provides cleaner code and a simpler method. Well, simpler for C anyway.

I used this method and ran the SQL connection program several times today. Worked every time.

This method of using strcat() has many uses outside of this one instance. This is a good way to get command line arguments processed, building result strings from collections of words, or any time that there are multitudes of things to stick together. However, I do recommend doing this in isolation without the rest of your program along to muddy the waters. Additionally, be ready to trial and error your findings and build from one working strcat() to the next. Code with patience, Padawan (and that means me).

Link to the easySoft site with ODBC and C instructions

Comments

[Andrew Clayton]

Quick tip.

strcat(3) should generally be avoided, unless you know 100% everything will fit.

Instead, in this case it would be better to use snprintf(3), though you can still get string truncation, or if you have GNU extensions you can use asprintf(3), which is like sprintf(3) but dynamically allocates the buffer.

Using snprint(3), your block of strcat()'s and malloc() can be reduced to...

#include <stdio.h>
...
char dsn[BUF_SZ];

snprintf(dsn, sizeof(dsn), "DSN=%s;UID=%s;PWD=%s",
         argv[1], argv[2], argv[3]);

[Dave]

Freaking brilliant! Worked like a charm, easier to read and configure. That's why I'm a noob.

Thanks,

[Andrew Clayton]

Cool, no problem!

[Dave]

Thanks, I'll have to give that a try.