Transcript

SQL Injection

10. Securing Oracle APEX – SQL Injection

 

>> Dan:  Let’s move on to some of the other vulnerabilities that you’re going to have to deal with as a developer, starting with SQL injection. We have two main classes of vulnerabilities with SQL injection. The first being use of substitution strings, you also have to watch out with dynamic SQL statements which hopefully would be rare. A lot of developers get in the habit of using them perhaps more than they should be or more than they need to. You should almost never have to use this really. 

 

[pause]

 

The first thing you need to watch out for is using substitution strings in concatenation. You have to learn the proper way to use bind variables or the V function. 

 

[pause]

 

The other thing you need to watch out for, if you have to execute dynamic SQL whether using DMS SQL or execute immediate, you need to sanitize using DBMS_ASSERT. Don’t trust the user input and of course make you’re using bind variables appropriately. 

 

[pause]

 

Let’s take a look at an SQL injection demo. This is one simple demo. 

 

[pause]

 

Create a new session here. Log in. 

 

[pause]

 

We’re going to go this customer report and to most folks this looks pretty standard. We type in a search string, and we get our results. The problem is in the way that this has been implemented. If we look inside this customer report, the developer chose to use a SQL query which is actually based on a PL/SQL function body returning a SQL query. He chose to build up the SQL query. Here’s a variable being declared and it said equal (=) to, and you can see what it’s doing, selecting some columns from a table where the last name equal (=). This is the wrong way to use bind variable syntax in your query. Ultimately we’re formulating a string and returning that string back out. 

 

[pause]

 

So what can happen when somebody starts to play with this? 

 

[pause]

 

They may decide to put in user input you didn’t expect. What I’ve just put in is test and I’ve ended a quote (“) here which causes a problem. 

 

Then really we could even without putting anything after that, just something funny like this, you hit “Go” and you get this error. And this is when a hacker’s eyes are going to light up and they’re going to say, wait a second. Let’s try something different. This time we’re going to union a select statement selecting from dull, see if we can get the syntax right. 

 

[pause]

 

Now we’re able to select from dull. Could we not work around maybe some of the security that’s been implemented here? 

 

[pause]

 

This one is supposed to just show us the customers that we search or appear. 

 

[pause]

 

What about all the customers? Now, the weird thing is that you can actually take this further rather than just getting customer-related data. You can learn more about the APEX workspace. 

 

[pause]

 

What I’ve put in now is actually a query going into one of the APEX dictionary views. We’re selecting information from APEX application page items. We hit go now. 

 

[pause]

 

That was too restrictive. 

 

Now I’m able to start finding items in an application. I can find out which items are hidden, which are protected, which are not. I can start to learn about your application to find the exploits. Once someone gets their foot in the door with a SQL injection vulnerability, it gets a lot easier for them to start hacking the rest of your app. So this is definitely something you have to watch out for.

 

[pause]

 

The way the bind variable should’ve brought in – should have been brought in, in this case – is like this. 

 

[pause]

 

The string’s being built up and the bind variables inside the string so when the query is executed, it’s inside. We apply that change, run it and try the same hack. Nothing comes back. So you have to learn how to use bind variables, the V function, execute immediate, dynamic SQL, all this stuff, you have to learn how to use it correctly. 

 

Copyright SkillBuilders.com 2017

 

×
Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!

 

×