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

 

[LucaMs]

Yes, instead; execute directly (PHPMyAdmin):

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

and the table will be dropped.

It is obvious that if RDC2 allowed the execution of multicommand queries, it would not eliminate the semicolon and passing that string as a parameter... that nice little thing would happen (because you cannot check if the parameter is really an Int).

 

[Harris]

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.

Try and try your theory... When you have a positive result of ERROR... THEN POST.
Otherwise, for example, 3 of us went round and round responding to an untested theory and hypothesis.
The experts have other better use of their time on this site...

 

[OliverA]

Yes, instead; execute directly (PHPMyAdmin):

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

and the table will be dropped.

And? What is your point? ; means end of statement. In order to prevent that to be used via sql injection, parameterized queries escape the ; . Has nothing to do with multiple statement execution, and 100% to do with escaping. PHPMyAdmin does not escape it, well, because it is valid SQL and you’re the admin. If that is what you want to do, it will unerringly comply with your wishes

 

[OliverA]

obvious that if RDC2 allowed the execution of multicommand queries, it would not eliminate the semicolon and passing that string as a parameter... that nice little thing would happen (because you cannot check if the parameter is really an Int).

No. First, any strings passed as an argument to a parameterized query are escaped. First step. Period. Done. That’s how it is. Then it will determine what the underlying column data type is. At that point it will, if necessary try to convert the string to the appropriate data type. The string is an argument. It will never ever make the sql Statement that it will be applied to dynamic. Again, this has nothing todo with the fact if or if not jdbc allows multi command queries. You think it just concatenates the parameter to the end of the string. Then executes it and fails since JDBC does not allow multi command SQL. Your wrong. It never ever just blindly adds the string you pass as a parameter to the sql statement. That is the whole point of parameterized queries. The whole point!

edit: spelling... it’s getting late

 

[LucaMs]

I don't agree but I'm satisfied with the two tests I did.

It was late for me too, it's almost dawn here!

 

[OliverA]

I don't agree

With your logic, a parameter of
“1 or 1=1” should then return all customers. It won’t, because it first is escaped in such a way that the or becomes ineffective. Try it in phpmyadmin:

SELECT * FROM Customer WHERE ID = 1 or 1=1

and then try it in jRDC2. This time no multiple sql statement executions, still different results. (you need to have more than one customer in the table in order for this to show properly)

Btw, if jdbc would be blocking your previous example because it does not allow multiple statements instead of escaping, the server should have thrown a syntax exception error message

 

[LucaMs]

With your logic, a parameter of
“1 or 1=1” should then return all customers. It won’t, because it first is escaped in such a way that the or becomes ineffective. Try it in phpmyadmin:

SELECT * FROM Customer WHERE ID = 1 or 1=1

and then try it in jRDC2. This time no multiple sql statement executions, still different results. (you need to have more than one customer in the table in order for this to show properly)

 

[Erel]

@OliverA is of course correct. You don't need to worry about SQL injections or escaping when you work with parameterized queries. This is a feature of the SQL engine. It will always treat the parameter as a single parameter.

It doesn't just replace the question mark with the parameter string.

 

[alwaysbusy]

That is the whole point of parameterized queries. The whole point!

Exactly!

 

[KMatle]

I started to use PHP from the beginning and I stay with it. Every hosted server comes with it. For RDC you'll need an own server (like VPS) which is hard to secure and to manage. Hosted solutions are managed and secured by pro's. The rest is upon you and what you prefer/like.

 

[Erel]

I started to use PHP from the beginning and I stay with it. Every hosted server comes with it. For RDC you'll need an own server (like VPS) which is hard to secure and to manage

Shared hosting are in many cases less secure than VPS. It is a single server running hundreds of sites and also the very thin margins can result in non-professional services.

 

[OliverA]

I started to use PHP from the beginning and I stay with it.

And hopefully you use parameterized queries with PHP (it has support for them), since they work just like they work in JDBC. Please note that technically, @LucaMs' assertion that given a parameterized SQL statement such as

SELECT * FROM Customer WHERE ID = ?;

an supplying it with the parameter of

"1; DROP TABLE Customer"

would produce

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

is wrong. The argument I made in post #24 above, even though I directly mention JDBC, applies to parameterized queries in general, no matter the platform. So be it Java, PHP, B4X, Kotlin, Ruby, Perl, Python, etc., parameterized queries would never create the above SQL. Again, that is the whole point of using parameterized queries. The one area that JDBC may be different from other implementations (it's been to long since I used PHP/Perl) is that is does block multi statement queries. So if you wanted to actually execute

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

using JDBC, JDBC would produce an exception.

 

[LucaMs]

Please note that technically, @LucaMs' assertion that given a parameterized SQL statement such as
an supplying it with the parameter of
would produce
is wrong.

If so, no sql injections would be possible, the argument simply would not exist.
https://www.w3schools.com/sql/sql_injection.asp

 

[Erel]

True. SQL injection is not possible with parameterized queries. SQL injection happens when developers don't use parameterized queries.

 

[OliverA]

If so, no sql injections would be possible, the argument simply would not exist.

How so? SQL injections are real. The link you provided shows you how. When you create your own SQL statements by concatenating SQL with user supplied information (as shown in that sample), you are exposing yourself to the risk of SQL injection. Creating your own SQL statements that way is also called creating dynamic SQL statements. When using parameterized queries, you are using fixed SQL statements that happen to include place holders for values that will be sanitized/escaped before they are added to the SQL statement. They are two different means of executing SQL statements. One very flexible, but very risky, the other a little bit more strict but a lot safer. Note: PHPMyAdmin uses the former (non-safe) version, since it makes sense that you as admin should be able to run any SQL you want, even SQL that wipes all your data. In a non-PHPMyAdmin type application, the latter (parameterized queries) should be used in order to prevent SQL injection. If you think you do need to use dynamic SQL in an application used by a user, you may want to think twice about it or you really really need to make sure that the input the user (or some other source as input, such as file, http request, etc.) gives you for that SQL statement is scrubbed of anything that could cause issues (in this case you are responsible for the scrubbing of the data - good luck). A good (but long) read: http://www.sommarskog.se/dynamic_sql.html (I don't know how well it translates, but everyone doing SQL and thinking about using dynamic SQL in a non-admin situation should read it or something like it).

Edit: Some grammar issues