Recently I had to update an old PERL program which, when it was originally written, had no sanitation of user input for SQL statements. The user input (from the web) was simply concatenated into SQL statements. This made it very vulnerable to SQL injection.
The SQL DBI used in the program did not allow parameterized queries and replacing it with a newer DBI would have required massive logic changes to the program. The solution was to figure out how to properly escape special characters present in the input. This turned out to be pretty simple if the input was surrounded by single quotes within the SQL statement. Assuming this is true, single quotes present in the input can be replace with with two single quotes. This will protect the SQL from injection.
Why? ANSI SQL says that a single quote is escaped by inserting an additional single quote directly before it. Escaping single quotes makes it very difficult if not impossible for the input to terminate the SQL string. However, this only works (at least on informix) if the input string is surround by single quotes in the SQL. Input strings surrounded by double quotes can not be escaped.
This method, combined with expanding function calls within strings, I was able to prevent SQL injection without major DBI and logic changes.
Tuesday, July 21, 2009
Thursday, July 9, 2009
Been a while
Its been a while since my last post and a lot has happened since.
Recently (in my spare time) I have been focusing on the second version of my game engine, Mercury.
Development is picking up speed and the project is really taking shape. My personal goal is to get the engine functioning enough to make a few short games. The previous version of the engine was successfully used by the UMBC game development club for their year long 3D project. I'll probably start writing about graphics programming more than anything else.
Recently (in my spare time) I have been focusing on the second version of my game engine, Mercury.
Development is picking up speed and the project is really taking shape. My personal goal is to get the engine functioning enough to make a few short games. The previous version of the engine was successfully used by the UMBC game development club for their year long 3D project. I'll probably start writing about graphics programming more than anything else.
Saturday, January 31, 2009
Writing secure SQL applications
When writing applications that make use of SQL, specifically applications that live on the web, security should be a high priority. Unfortunately security usually ends up just an afterthought. In my experience reviewing and maintaining web applications written by others, I have found that they take little to no precaution against SQL injection.
SQL injection is the practice of crafting user input to alter the function of a dynamically generated SQL statement. In web based langagues, SQL statements are usually constructed using string concatenation to combine the query statements with the query values. This can lead to very dangerous conditions. Consider the following simple query.
Assuming the email address is inserted into the query using string concatenation it is trivial to alter how the query functions. If I entered my e-mail as:
myemail@email.com'; drop table usertable; --
The resulting query would be:
select username from user_table where email='myemail@email.com'; drop table usertable; --'
This would instruct the SQL server to drop the table (assuming the application has adequate permissions). Of course you can construct any statement you wish to manipulate the SQL server.
The usual protection against this type of attack to to escape special characters such as ' and ;. This can help improve security but is not fool proof.
Consider the following:
select username from user_table where id=123456
If the user id could be manipulated by the user it would be possible to make the id something like:
123456 and 1=(delete from user table where id != 123456)
The resulting query would be:
select username from user_table where id=123456 and 0<(delete from user_table where id != 123456)
This would instruct the SQL server to delete all users who's id is not 123456. Notice we have not used any special characters so escaping would not help in this situation.
Now there is a rather nice solution to these problems, parameterized queries. Parameterized queries allow you to prepare queries and then send in values at execution time.
Using the last example, the parameterized query would look like this:
select username from user_table where id=?
Parameters are usually indicated with a ? but may depend on the SQL library.The query is prepared by using something similar to:
We only need to do that once.
Then we can execute it as many times as we want with something similar to:
$result = $query->execute("123456");
The neat thing with this is that the database library will handle inserting the parameters into the query. There is no need to escape special characters using this method. I would argue this using this method makes SQL injection extremely difficult, if not impossible.
Using parameterized queries is usually a little more work than just concatenating strings, but the benefits are well worth the extra effort.
I have used parameterized queries using both PERL and PHP with Informix and mySQL databases. The PERL module is DBI, the php class is mysqli. They function differently but the concept is the same.
SQL injection is the practice of crafting user input to alter the function of a dynamically generated SQL statement. In web based langagues, SQL statements are usually constructed using string concatenation to combine the query statements with the query values. This can lead to very dangerous conditions. Consider the following simple query.
select username from user_table where email='myemail@email.com'
Assuming the email address is inserted into the query using string concatenation it is trivial to alter how the query functions. If I entered my e-mail as:
myemail@email.com'; drop table usertable; --
The resulting query would be:
select username from user_table where email='myemail@email.com'; drop table usertable; --'
This would instruct the SQL server to drop the table (assuming the application has adequate permissions). Of course you can construct any statement you wish to manipulate the SQL server.
The usual protection against this type of attack to to escape special characters such as ' and ;. This can help improve security but is not fool proof.
Consider the following:
select username from user_table where id=123456
If the user id could be manipulated by the user it would be possible to make the id something like:
123456 and 1=(delete from user table where id != 123456)
The resulting query would be:
select username from user_table where id=123456 and 0<(delete from user_table where id != 123456)
This would instruct the SQL server to delete all users who's id is not 123456. Notice we have not used any special characters so escaping would not help in this situation.
Now there is a rather nice solution to these problems, parameterized queries. Parameterized queries allow you to prepare queries and then send in values at execution time.
Using the last example, the parameterized query would look like this:
select username from user_table where id=?
Parameters are usually indicated with a ? but may depend on the SQL library.The query is prepared by using something similar to:
$query = $db->prepare("select username from user_table where id=?");
We only need to do that once.
Then we can execute it as many times as we want with something similar to:
$result = $query->execute("123456");
The neat thing with this is that the database library will handle inserting the parameters into the query. There is no need to escape special characters using this method. I would argue this using this method makes SQL injection extremely difficult, if not impossible.
Using parameterized queries is usually a little more work than just concatenating strings, but the benefits are well worth the extra effort.
I have used parameterized queries using both PERL and PHP with Informix and mySQL databases. The PERL module is DBI, the php class is mysqli. They function differently but the concept is the same.
Sunday, November 16, 2008
PS3 MP4 Encoding with Linux
After a lot of reading, searching, and trial and error, I have come up with a fairly simple way to convert video and audio content into an h264 and aac stream. The stream is packed into an MP4 file and can be streamed to a PS3 via a media server such as MediaTomb.
The following scripts makes it a pretty automated process. It relies on mencoder, and MP4Box. I have been using it to convert DVD vob files into smaller MP4 files, with nearly the same quality. I can't actually tell a difference from the original DVD source.
#!/bin/bash
VIDEOFILTER=crop=704:480:8:0
ENCOPTS=subq=5:bframes=4:b_pyramid:weight_b:psnr:frameref=3:bitrate=$2:turbo=1:me=hex:partitions=all:8x8dct:qcomp=0.7:threads=auto
mencoder -v \
$1 \
-alang en \
-vf $VIDEOFILTER \
-ovc x264 -x264encopts $ENCOPTS:pass=1:turbo=1 \
-ofps 24000/1001 \
-vobsubout "$3_subtitles" -vobsuboutindex 0 -slang en \
-passlogfile "$3.log" \
-oac copy \
-o /dev/null
mencoder -v \
$1 \
-alang en \
-vf $VIDEOFILTER \
-ovc x264 -x264encopts $ENCOPTS:pass=2 \
-oac faac -faacopts object=1:tns:quality=150 \
-passlogfile "$3.log" \
-ofps 24000/1001 \
-o "$3.avi"
MP4Box -aviraw video "$3.avi"
MP4Box -aviraw audio "$3.avi"
mv "$3_audio.raw" "$3_audio.aac"
rm "$3.mp4"
MP4Box -isma -hint -add "$3_video.h264#Video:fps=23.976" -add "$3_audio.aac" "$3.mp4"
rm "$3_video.h264" "$3_audio.aac"
Running the script involves something like...
./x264Encode2.sh Terminator2.vob 3800 Terminator2
The first argument is the source file, the second is the target bitrate, and the 3rd is the target file. I do not put an extension on the target as the script will do this. A bitrate of 3800 seems high enough to produce an encode that looks nearly identical to the source DVD.
You will probably need to tweak the VIDEOFILTER crop filter according to your video source. You can run mplayer on your source using -vf cropdetect to determine the correct cropping arguments. Of course you can also change the ENCOPTS to your liking, although I find the current options acceptable.
Once the oncode is finished you have a nice MP4 file. The avi file is left after the encode in-case something go wrong so you don't have to re-encode the source. If everything goes ok, you can delete this file.
Let me know what you think.
EDIT: The PS3 seems to be picky about the video resolutions, be careful when cropping to strange resolutions. I'll try to investigate this more, as I ran into a problem with a cropped wide screen video. Removing or tweaking the crop made a short test encode work.
The following scripts makes it a pretty automated process. It relies on mencoder, and MP4Box. I have been using it to convert DVD vob files into smaller MP4 files, with nearly the same quality. I can't actually tell a difference from the original DVD source.
#!/bin/bash
VIDEOFILTER=crop=704:480:8:0
ENCOPTS=subq=5:bframes=4:b_pyramid:weight_b:psnr:frameref=3:bitrate=$2:turbo=1:me=hex:partitions=all:8x8dct:qcomp=0.7:threads=auto
mencoder -v \
$1 \
-alang en \
-vf $VIDEOFILTER \
-ovc x264 -x264encopts $ENCOPTS:pass=1:turbo=1 \
-ofps 24000/1001 \
-vobsubout "$3_subtitles" -vobsuboutindex 0 -slang en \
-passlogfile "$3.log" \
-oac copy \
-o /dev/null
mencoder -v \
$1 \
-alang en \
-vf $VIDEOFILTER \
-ovc x264 -x264encopts $ENCOPTS:pass=2 \
-oac faac -faacopts object=1:tns:quality=150 \
-passlogfile "$3.log" \
-ofps 24000/1001 \
-o "$3.avi"
MP4Box -aviraw video "$3.avi"
MP4Box -aviraw audio "$3.avi"
mv "$3_audio.raw" "$3_audio.aac"
rm "$3.mp4"
MP4Box -isma -hint -add "$3_video.h264#Video:fps=23.976" -add "$3_audio.aac" "$3.mp4"
rm "$3_video.h264" "$3_audio.aac"
Running the script involves something like...
./x264Encode2.sh Terminator2.vob 3800 Terminator2
The first argument is the source file, the second is the target bitrate, and the 3rd is the target file. I do not put an extension on the target as the script will do this. A bitrate of 3800 seems high enough to produce an encode that looks nearly identical to the source DVD.
You will probably need to tweak the VIDEOFILTER crop filter according to your video source. You can run mplayer on your source using -vf cropdetect to determine the correct cropping arguments. Of course you can also change the ENCOPTS to your liking, although I find the current options acceptable.
Once the oncode is finished you have a nice MP4 file. The avi file is left after the encode in-case something go wrong so you don't have to re-encode the source. If everything goes ok, you can delete this file.
Let me know what you think.
EDIT: The PS3 seems to be picky about the video resolutions, be careful when cropping to strange resolutions. I'll try to investigate this more, as I ran into a problem with a cropped wide screen video. Removing or tweaking the crop made a short test encode work.
Wednesday, September 24, 2008
Callbacks, the trick pointer you execute
At work I tend to develop programs using C#, which I don't particularly like but it does have some neat features. One of the features that caught my attention are delegates. Delegates are essentially function pointers (actually they do a little more than that). A function pointer is just a pointer to a function much like a pointer to a variable. I found it extremely interesting how delegates are used in ASP .NET for event driven processes.
I began wondering if I could implement similar systems in my own projects using C++.
Callbacks in C++ provide a way to call functions by executing function pointers. In C# delegates are type safe, this helps ensure you don't end up passing and receiving junk data to and from your function. Callbacks are not quite as nice, they are not necessarily typed and usually make heavy use of void pointers and unsafe type casting. To achieve typed callbacks in C++ I started making a templated callback class that allows for typed arguments and typed returns. Using a templated library helps ensure type safety and provides excellent code reuse when using callbacks with various types. Using the templated callback library, making a callback is as easy as...
void myFunction(int x)
{
...
}
Callback1<int> myCallback( myFunction );
Executing the callback is then as simple as
myCallback(7);
Why would we want to go through all this trouble just to run myFunction? Well, think about a system that executes functions but the functions are not known until runtime. Event processing tends to be one such system.
For event processing you could have a function that contains one giant if else or case statement for every possible event. Or you could make a system where you register functions with a type of event, and when the specific event occurs, a callback to your function is executed. This type of event system is exactly what I used callbacks for.
In the Stepmania Online game server I am developing, players are able to enter commands from the chat interface to manipulate the server. Since the events the server is handling are text strings, a switch statement is not an option. With a couple commands, multiple if else statements is an ok solution. However once the number of commands begins to grow, the if else statements become very long and ugly. To solve this I made a list of callbacks, each associated with a string command. I then "registered" each function with a string command by creating a callback to the function and adding it to the list along with the associated string command.
When a command is received, the program simply loops through the list of command stringsand callback. When a match is found between the stored string and the command, the callback associated with the command is executed. No giant if else statement required!
The real power in this system is that extra commands can be created by just compiling and linking in extra commands. This means, the game server could be released as a library and the chat commands can be expand uppon without touching or recompiling the core of the server. Very useful!
I have found that it takes a while to fully grasp the concept of callbacks, but it is well worth the effort.
I began wondering if I could implement similar systems in my own projects using C++.
Callbacks in C++ provide a way to call functions by executing function pointers. In C# delegates are type safe, this helps ensure you don't end up passing and receiving junk data to and from your function. Callbacks are not quite as nice, they are not necessarily typed and usually make heavy use of void pointers and unsafe type casting. To achieve typed callbacks in C++ I started making a templated callback class that allows for typed arguments and typed returns. Using a templated library helps ensure type safety and provides excellent code reuse when using callbacks with various types. Using the templated callback library, making a callback is as easy as...
void myFunction(int x)
{
...
}
Callback1<int> myCallback( myFunction );
Executing the callback is then as simple as
myCallback(7);
Why would we want to go through all this trouble just to run myFunction? Well, think about a system that executes functions but the functions are not known until runtime. Event processing tends to be one such system.
For event processing you could have a function that contains one giant if else or case statement for every possible event. Or you could make a system where you register functions with a type of event, and when the specific event occurs, a callback to your function is executed. This type of event system is exactly what I used callbacks for.
In the Stepmania Online game server I am developing, players are able to enter commands from the chat interface to manipulate the server. Since the events the server is handling are text strings, a switch statement is not an option. With a couple commands, multiple if else statements is an ok solution. However once the number of commands begins to grow, the if else statements become very long and ugly. To solve this I made a list of callbacks, each associated with a string command. I then "registered" each function with a string command by creating a callback to the function and adding it to the list along with the associated string command.
When a command is received, the program simply loops through the list of command stringsand callback. When a match is found between the stored string and the command, the callback associated with the command is executed. No giant if else statement required!
The real power in this system is that extra commands can be created by just compiling and linking in extra commands. This means, the game server could be released as a library and the chat commands can be expand uppon without touching or recompiling the core of the server. Very useful!
I have found that it takes a while to fully grasp the concept of callbacks, but it is well worth the effort.
Thursday, September 4, 2008
Revisiting old code
This past week I began working on Mercury2, which will be almost complete rewrite of my first game engine Mercury. So far I have only worked on the render window. While working on it i decided to look at my old code from Mercury1 to try to figure out how I had done it. When looking back at old code, its amazing to see how much your coding style changes and improves over the years. Its been over three years since I wrote the windowing code for Mercury and it certainly shows.
Looking at my old window code, it was clear I didn't have any understanding of what I was doing when I wrote it. Actually a lot of it was taken (and giving credit to) from NeHe lesson 1 and was modified to be a little more flexible (in my mind at the time). The result worked well, but the code was a pretty convoluted. I decided it was best to sit down and review my old code to try to figure out exactly how it was supposed to work. So with a long of review of my old windowing code, and some heavy reading of MSDN documentation I developed a much more solid window system.
Moving forward in the project I think it will be difficult to properly design the project knowing I have large amounts of "old code that works" that I could fall back on.
Looking at my old window code, it was clear I didn't have any understanding of what I was doing when I wrote it. Actually a lot of it was taken (and giving credit to) from NeHe lesson 1 and was modified to be a little more flexible (in my mind at the time). The result worked well, but the code was a pretty convoluted. I decided it was best to sit down and review my old code to try to figure out exactly how it was supposed to work. So with a long of review of my old windowing code, and some heavy reading of MSDN documentation I developed a much more solid window system.
Moving forward in the project I think it will be difficult to properly design the project knowing I have large amounts of "old code that works" that I could fall back on.
Sunday, August 17, 2008
Open source development vs. business development
Over the past eight months at my current job I have had an opportunity to work with various people in designing new programs to fit their business needs. While working on the projects, one aspect has become clear, developing free open source software is vastly different from developing software for your employer.
The writing of the software isn't so different. You still need to plan your software(based on requirements), write it, and then test it. In my experience, many open source projects are planned rather poorly. They are then initially coded up rather quickly to a point of working "well enough". The programs tend to be somewhat tested by the developers but rely heavily on "community testing." This often results in more than a few negative user experiences. Development of many open source programs begin to stagnate after a while and end up in a continuous state of development, never really being done.
In business environments, software development I have found works differently. Planning is done with a "client". Rarely does the client actually know the full requirements of the software they are requesting. I generally try to understand the client's current business process they are trying to enhance/automate etc. While discussing the business process with the client, we try to determine the actual requirements based on the business process. (I have found that the client doesn't always know the complete process or may forget parts. Its good to involve a few people that know and work with the process.) One fact about the software requirements, they will change throughout a project, no matter what.
After the requirements are known, the real work can begin. However unlike most open source software there is a very real and probably very near (closer than you would like) deadline. There is little time to be spent refactoring poor inflexible code. Its very useful to take the time upfront to really think about your approach, you also need to plan for testing. I have found that testing generally takes just about as long as writing the program. This is not necessarily because of a large number of bugs but rather who is testing and how much attention they give to it. I like to involve the client in the testing process as they know the process the best. They are the ones who will be able to spot logic errors in the process. The clients tend to think about the process much more differently than myself as the programmer, what was clear to them may not have been clear to me. Anyhow, testing... allow ample time for it. I often find myself and the client testing right up to the deadline. Even after the deadline problems are often found while the software is being used in a production environment. Testing is very important when your software will have a real impact on businesses and user impressions of that business.
The writing of the software isn't so different. You still need to plan your software(based on requirements), write it, and then test it. In my experience, many open source projects are planned rather poorly. They are then initially coded up rather quickly to a point of working "well enough". The programs tend to be somewhat tested by the developers but rely heavily on "community testing." This often results in more than a few negative user experiences. Development of many open source programs begin to stagnate after a while and end up in a continuous state of development, never really being done.
In business environments, software development I have found works differently. Planning is done with a "client". Rarely does the client actually know the full requirements of the software they are requesting. I generally try to understand the client's current business process they are trying to enhance/automate etc. While discussing the business process with the client, we try to determine the actual requirements based on the business process. (I have found that the client doesn't always know the complete process or may forget parts. Its good to involve a few people that know and work with the process.) One fact about the software requirements, they will change throughout a project, no matter what.
After the requirements are known, the real work can begin. However unlike most open source software there is a very real and probably very near (closer than you would like) deadline. There is little time to be spent refactoring poor inflexible code. Its very useful to take the time upfront to really think about your approach, you also need to plan for testing. I have found that testing generally takes just about as long as writing the program. This is not necessarily because of a large number of bugs but rather who is testing and how much attention they give to it. I like to involve the client in the testing process as they know the process the best. They are the ones who will be able to spot logic errors in the process. The clients tend to think about the process much more differently than myself as the programmer, what was clear to them may not have been clear to me. Anyhow, testing... allow ample time for it. I often find myself and the client testing right up to the deadline. Even after the deadline problems are often found while the software is being used in a production environment. Testing is very important when your software will have a real impact on businesses and user impressions of that business.
Subscribe to:
Posts (Atom)