JanPRO Well-Known Member Licensed User Longtime User Sep 19, 2018 #1 Hi, related to SQL injections Erel has stated, that Erel said: Parameterized queries are also safe (SQL.ExecQuery2, ExecNonQuery2). Click to expand... 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
Hi, related to SQL injections Erel has stated, that Erel said: Parameterized queries are also safe (SQL.ExecQuery2, ExecNonQuery2). Click to expand... 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 Expert Licensed User Longtime User Sep 19, 2018 #2 https://www.journaldev.com/2489/jdbc-statement-vs-preparedstatement-sql-injection-example has a nice explanation of the advantages of PreparedStatements. Upvote 0
https://www.journaldev.com/2489/jdbc-statement-vs-preparedstatement-sql-injection-example has a nice explanation of the advantages of PreparedStatements.
Harris Expert Licensed User Longtime User Sep 19, 2018 #3 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. Upvote 0
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 Well-Known Member Licensed User Longtime User Sep 19, 2018 #4 Obligatory: https://xkcd.com/327/ Also: https://www.w3schools.com/sql/sql_injection.asp Upvote 0
JanPRO Well-Known Member Licensed User Longtime User Sep 19, 2018 #5 @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 Harris said: It is also safer as the SQL commands are set in the server side. Click to expand... 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 Last edited: Sep 19, 2018 Upvote 0
@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 Harris said: It is also safer as the SQL commands are set in the server side. Click to expand... 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 Expert Licensed User Longtime User Sep 19, 2018 #6 JanPRO said: main benefit concerning security is the escaping of special characters Click to expand... 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). JanPRO said: I guess, nobody would ever send whole SQL query to a server ... Click to expand... We really need a sarcasm tag in this forum... Upvote 0
JanPRO said: main benefit concerning security is the escaping of special characters Click to expand... 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). JanPRO said: I guess, nobody would ever send whole SQL query to a server ... Click to expand... We really need a sarcasm tag in this forum...
Erel B4X founder Staff member Licensed User Longtime User Sep 20, 2018 #7 JanPRO said: I guess, nobody would ever send whole SQL query to a server ... Click to expand... Look here: https://www.b4x.com/android/forum/threads/connect-android-to-mysql-database-tutorial.8339/#content Upvote 0
JanPRO said: I guess, nobody would ever send whole SQL query to a server ... Click to expand... Look here: https://www.b4x.com/android/forum/threads/connect-android-to-mysql-database-tutorial.8339/#content
LucaMs Expert Licensed User Longtime User Mar 12, 2020 #8 Harris said: 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. Click to expand... 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 said: 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. Click to expand... 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 Expert Licensed User Longtime User Mar 12, 2020 #9 Would it Fail? --- Try it... Would "1; DROP TABLE Customer" satisfy the Where ID = ? param...? I have my doubts.... Upvote 0
Would it Fail? --- Try it... Would "1; DROP TABLE Customer" satisfy the Where ID = ? param...? I have my doubts....
LucaMs Expert Licensed User Longtime User Mar 12, 2020 #10 Harris said: Would it Fail? --- Try it... Would "1; DROP TABLE Customer" satisfy the Where ID = ? param...? I have my doubts.... Click to expand... 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 Last edited: Mar 12, 2020 Upvote 0
Harris said: Would it Fail? --- Try it... Would "1; DROP TABLE Customer" satisfy the Where ID = ? param...? I have my doubts.... Click to expand... 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 Expert Licensed User Longtime User Mar 12, 2020 #11 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
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 Expert Licensed User Longtime User Mar 12, 2020 #12 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
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 Expert Licensed User Longtime User Mar 12, 2020 #13 LucaMs said: What happens if I call it passing: "1; DROP TABLE Customer Click to expand... 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. LucaMs said: It will work (and drop the table) because there aren't checks in config.properties Click to expand... The config file has to do zero checking. What checks are you expecting? Upvote 0
LucaMs said: What happens if I call it passing: "1; DROP TABLE Customer Click to expand... 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. LucaMs said: It will work (and drop the table) because there aren't checks in config.properties Click to expand... The config file has to do zero checking. What checks are you expecting?
Harris Expert Licensed User Longtime User Mar 12, 2020 #14 OliverA said: “1; DROP TABLE Customer”. Click to expand... That is what I assumed... No such entry... - Particularly where the where field is an INT... Upvote 0
OliverA said: “1; DROP TABLE Customer”. Click to expand... That is what I assumed... No such entry... - Particularly where the where field is an INT...
LucaMs Expert Licensed User Longtime User Mar 12, 2020 #15 Tested (not only that "injection"). It does not work because RDC2 does not execute multiple command queries; just for this reason, not because: Harris said: Particularly where the where field is an INT.. Click to expand... nor because: OliverA said: It is escaped Click to expand... 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) OliverA said: What checks are you expecting? Click to expand... The same you do in PHP. Upvote 0
Tested (not only that "injection"). It does not work because RDC2 does not execute multiple command queries; just for this reason, not because: Harris said: Particularly where the where field is an INT.. Click to expand... nor because: OliverA said: It is escaped Click to expand... 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) OliverA said: What checks are you expecting? Click to expand... The same you do in PHP.
Harris Expert Licensed User Longtime User Mar 12, 2020 #16 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
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 Expert Licensed User Longtime User Mar 12, 2020 #17 LucaMs said: The same you do in PHP. Click to expand... B4X (hopefully and thankfully) ain't PHP... I escaped once... thats why I hide out here... Upvote 0
LucaMs said: The same you do in PHP. Click to expand... B4X (hopefully and thankfully) ain't PHP... I escaped once... thats why I hide out here...
OliverA Expert Licensed User Longtime User Mar 12, 2020 #18 LucaMs said: The same you do in PHP. Click to expand... And what is that? LucaMs said: nor because Click to expand... It is because of escaping. This has zero to do with executing multiple command queries. Upvote 0
LucaMs said: The same you do in PHP. Click to expand... And what is that? LucaMs said: nor because Click to expand... It is because of escaping. This has zero to do with executing multiple command queries.
LucaMs Expert Licensed User Longtime User Mar 12, 2020 #19 Harris said: So... You got us all worked up for nothing? Click to expand... 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. Upvote 0
Harris said: So... You got us all worked up for nothing? Click to expand... 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 Expert Licensed User Longtime User Mar 12, 2020 #20 OliverA said: It is because of escaping. This has zero to do with executing multiple command queries. Click to expand... Yes, instead; execute directly (PHPMyAdmin): SELECT * FROM Customer WHERE ID = 1; DROP TABLE Customer and the table will be dropped. Upvote 0
OliverA said: It is because of escaping. This has zero to do with executing multiple command queries. Click to expand... Yes, instead; execute directly (PHPMyAdmin): SELECT * FROM Customer WHERE ID = 1; DROP TABLE Customer and the table will be dropped.
