Programming for Oracle on Linux, Part 2

When Perl isn't fast enough, you can have applications interact with Oracle databases by using C.
Summary

As I pointed out in the first part of this series, interaction with Oracle databases can be as simple as a few hundred lines of code. Between our C source and Makefile, we've written a mostly complete application with a decent amount of exception handling and comments in a total of 394 lines of code. To be more complete, we could add signal handling and some additional database consistency checks, but for our modest application these things can be done in a small amount of code as well. Additionally, as with Perl, it would be possible to link against other database connection libraries, such as the libmysqlclient library for MySQL or PostgreSQL's libpq, and use wrapper functions to make our interfaces more generic. But that's another topic for another time.

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Pls. break the long line in PRE-tag into two lines

Anonymous's picture

RyanOrdway, if you break this following line:

sprintf(sql_string, "SELECT username, first_name, last_name, address_line1, address_line2,
city, state, zipcode, phone, age FROM customer_data WHERE status=1");

into 2 lines, as in:

sprintf(sql_string, "SELECT username, first_name, last_name, address_line1, address_line2, "
"city, state, zipcode, phone, age FROM customer_data WHERE status=1");

you would avoid the horizontal scroll.

Re: Programming for Oracle on Linux, Part 2

Anonymous's picture

Seconding the previous post on bind variables...

What does your code do when one of the fields contains a single apostrophe (e.g. a last name of "O'Hara")? What about buffer overflow attacks (i.e. why aren't you checking for overflow when sprintf'ing into them)?

Beyond that, do you initialize your variables to zero-length strings? Why not make a struct to hold the customer record, something like:

typedef struct stCustomer {
char username[32];
char first_name[256];
char last_name[256];
char address_line1[256];
char address_line2[256];
char city[256];
char state[3];
char zipcode[256];
char phone[256];
int age;
} CUSTOMER ;

and do something like the following when using it?

// ...get the command-line args
CUSTOMER cust ;
memset(&cust, 0, sizeof(CUSTOMER)) ;
...
case 'L':
snprintf(cust.last_name, sizeof(cust.last_name), "%s", optarg) ;
break ;
...

// ...create the record
create_customer_record(&cust) ;

?

BIND VARIABLES!

Anonymous's picture

When Perl is not fast enough...

The kind of SQL statements you have in your code will become a performance bottleneck significantly before Perl does (if that ever is an issue...)

You still have not heard about bind variables, have you?

In addition to making your code invulnerable to SQL injection attacks, it will be faster, especially on Oracle.

In any case, when you are developing on Oracle, you should move all your SQL into stored procedures and not have them in the application code. Much easier to maintain, faster to develop, better reusabilty, runs faster.

Just my two cents.

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix