B4J Question SQL injections: What makes parameterized queries safe?

[JanPRO]

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

 

[Daestrum]

https://www.journaldev.com/2489/jdbc-statement-vs-preparedstatement-sql-injection-example has a nice explanation of the advantages of PreparedStatements.

 

[Harris]

https://www.b4x.com/android/forum/t...ation-of-rdc-remote-database-connector.61801/

Why use jRDC2?

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.

 

[Jeffrey Cameron]

Obligatory: https://xkcd.com/327/

Also: https://www.w3schools.com/sql/sql_injection.asp

 

[JanPRO]

@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 . I guess, nobody would ever send whole SQL query to a server ...

Jan

 

[OliverA]

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...

 

[Erel]

I guess, nobody would ever send whole SQL query to a server ...

Look here: https://www.b4x.com/android/forum/threads/connect-android-to-mysql-database-tutorial.8339/#content

 

[LucaMs]

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?

 

[Harris]

Would it Fail? --- Try it...

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

 

[LucaMs]

Would it Fail? --- Try it...

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

It will work (and drop the table) because there aren't checks in config.properties.

The full query executed will be:

SELECT * FROM Customer WHERE ID = 1; DROP TABLE Customer

 

[OliverA]

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).

 

[Harris]

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...

 

[OliverA]

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?

 

[Harris]

“1; DROP TABLE Customer”.

That is what I assumed... No such entry... - Particularly where the where field is an INT...

 

[LucaMs]

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.

 

[Harris]

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...

 

[Harris]

The same you do in PHP.

B4X (hopefully and thankfully) ain't PHP...
I escaped once... thats why I hide out here...

 

[OliverA]

The same you do in PHP.

And what is that?

nor because

It is because of escaping. This has zero to do with executing multiple command queries.

 

[LucaMs]

So... You got us all worked up for nothing?

I also worked for nothing, since for the moment I don't need to use RDC2 ?

I have already forgotten ? how I now got to the RDC2 tutorial (which I had already known and tried) but I had those doubts and I wanted to try.

 

[LucaMs]

It is because of escaping. This has zero to do with executing multiple command queries.

Yes, instead; execute directly (PHPMyAdmin):

SELECT * FROM Customer WHERE ID = 1; DROP TABLE Customer

and the table will be dropped.