B4J Question SQL injections: What makes parameterized queries safe?

JanPRO

Well-Known Member
Licensed User
Longtime User
Hi,

related to SQL injections Erel has stated, that
Parameterized queries are also safe (SQL.ExecQuery2, ExecNonQuery2).
He also has mentioned the same in his SQL video tutorial.

But why parameterized queries are considered to be safe? Does this method just escape special characters? Or is there another security mechanism behind the function?

Jan
 

JanPRO

Well-Known Member
Licensed User
Longtime User
@Daestrum
Thank you very much, that's a great article.
To sum it up, the main benefit concerning security is the escaping of special characters.

@Harris
It is also safer as the SQL commands are set in the server side.
Honestly, I don't know a solution where the SQL commands are not set on the server side o_O. I guess, nobody would ever send whole SQL query to a server ...

Jan
 
Last edited:
Upvote 0

OliverA

Expert
Licensed User
Longtime User
main benefit concerning security is the escaping of special characters
Another way of looking at it, it separates code from data in such a way that data cannot be interpreted as code. And that is a very good thing in this case (SQL).

I guess, nobody would ever send whole SQL query to a server ...
We really need a sarcasm tag in this forum...
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
jRDC2 can work with any database that provides a JDBC driver. All popular databases are supported.
It is much more powerful than the PHP based solution and it has excellent performance.
It is also safer as the SQL commands are set in the server side.
Say we have this query wrote in the config.properties:

sql.select_customer=SELECT * FROM Customer WHERE ID = ?;

What happens if I call it passing:

"1; DROP TABLE Customer"

as parameter?
 
Upvote 0

Harris

Expert
Licensed User
Longtime User
Would it Fail? --- Try it...

Would "1; DROP TABLE Customer" satisfy the Where ID = ? param...? I have my doubts....
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Wrong, LucasM. Look at the code of jRDC2. The string in the config properties becomes the SQL Statement portion and what you are passing becomes the parameter that will be escaped by the method used by the server (a parameterized query).
 
Upvote 0

Harris

Expert
Licensed User
Longtime User
Hummm, was it tried? At this point, seems not.
I don't know enough about this - just it seems illogical to me. The ? is expecting one param - not a string of params...
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
What happens if I call it passing:

"1; DROP TABLE Customer
It is escaped. Period. It’s a single parameter, that never becomes two. In this case the query would look for a customer who’s id is “1; DROP TABLE Customer”. Most likely, there is no such customer, so an empty result set will be returned and the customer table has not been harmed.


It will work (and drop the table) because there aren't checks in config.properties
The config file has to do zero checking. What checks are you expecting?
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
Tested (not only that "injection").

It does not work because RDC2 does not execute multiple command queries; just for this reason, not because:
Particularly where the where field is an INT..

nor because:
It is escaped



I tried also a different injection and this didn't work because of "escaping".

It's ok (though I'm not a very good hacker 😄, so I cannot be sure)


What checks are you expecting?
The same you do in PHP.
 
Upvote 0

Harris

Expert
Licensed User
Longtime User
So... You got us all worked up for nothing?

Not so, it is good to raise such questions - otherwise we sit here wondering without knowing what is TRUE and what is FALSE.
Thank @OliverA for setting this straight...
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Upvote 0
Top