Friday, February 24, 2006

More username fun...

Last week, it was the error caused by the dot in the username.
This week we got a slightly different variation on the theme.

A user kept getting a pop up window with the message
ORA-00933: SQL command not properly ended

Again, we trapped the SQL being executed and discovered that it was reading in the username.

The user's surname in this case was O'Neill.

The username included the apostrophe.

Wonder what we could get next week!


Blogger shrek said...

Wonder what we could get next week!

remeber the saying "be careful what you wish for, you just might get it.";-)

Friday, February 24, 2006 1:31:00 pm  
Blogger Lisa said...

I realised that right after I had published the post!

Friday, February 24, 2006 2:14:00 pm  
Blogger roobaron said...

Be very careful letting that type of stuff get to the database not checked as SQL injection could be nasty surprise.
what if I say my username is paul';truncate table sys.obj$;

The application needs to parse all user input before getting near the database, a bit of regex will do the trick :)

Have Fun


Saturday, February 25, 2006 3:09:00 am  
Blogger Danny R said...

Hi Lisa, I used to get ora 00933 all the time on my placement last year. We had a field that accepted user comments and one of the comments they used to write was worded along the lines of

dave's test

In the end we modified the code that accepted the comments to escape the apostrophes if they were present!

Oh congrats on your degree I know what you went through!

My final year blog

Sunday, February 26, 2006 8:06:00 pm  
Anonymous Cleric said...

Put an @ in your username or password and see what happens. This drove me nuts for weeks.

Sunday, February 26, 2006 9:36:00 pm  
Anonymous Anonymous said...

Either outside of the DB or inside of it I run a REPLACE function and just use Dynamic SQL to execute the command. Just depends on what you need.

Wednesday, March 01, 2006 2:11:00 am  
Anonymous Anonymous said...

I remember company names like "Newell & Budge" causing some funny problems a few years ago

Wednesday, March 01, 2006 7:55:00 pm  
Anonymous Jason McIntosh said...

Related to all of this, keep in mind on developing applications, a simple fix. USE PREPARED STATEMENTS.

I can't say and emphasize that strongly enough. Yes, you can parse through and fix a lot of the issues, but what happens if the user uses some other special character that causes an issue with the scripts? You need to use preparedstatements where-ever possible. Then, simply calling setString("FIELDNAME", value); allows Oracle to parse the values. PHP/Java/numerous other languages support this system.

Related to that, performance becomes increasingly better with longer usage of prepared statements. If you've got a query you're running multiple times, you really need to use prepared statements, as the database only parses the sql statement once instead of multiple times.

Friday, March 03, 2006 9:49:00 pm  

Post a Comment

<< Home