Before diving into the topic let me acknowledge all of you for having read and visited so many times this article about SQL tricks:
The “Query of Queries” Trick to Automatically Produce SQL Statements
A super-effective trick useful while working with many DBs and webservices
medium.com
You all made me really happy by appreciating it so much. I’d never expected such a success.
Thanks.
The interest you put in it made me wanna go deeper into a related topic, the SQL injection, which is easily mistaken with what I suggested in that article.
Running SQL Statements within Code
When connecting SQL and other programming languages:
SQL statements are treated as strings.
Strings that contain SQL commands, parameter placeholders and so on. Strings are manipulated, parsed and passed to the methods of the connector.
Let’s look at this Spring example, using Java:
There are:
a NamedParameterJdbcTemplate autowired object, the connector
a map of parameters ( MapSqlParamters ) that is going to be filled with the parameters to be used in the query, in a key-value map fashion. Parameters can be of any primitive type, wrapper classes (like Integer), strings or even lists of those.
a string with a parameterized query. Parameters are indicated with their placeholder, in the form of :paramName , which is also their key in the parameters map.
The call to the .queryForList() method runs the query. The method takes the query string, the parameters map, and the class to which map the resultset.
This is just one way of doing this, and without losing generality, we can say that the basic logic is always the same:
provide a query with parameters placeholders
provide the parameters
let the chosen library fill in the placeholders and produce a complete query in the form of a string.
Then run it.
SQL Injection
Since the output is just a string that is going to be parsed as a statement and then performed, it is impossible to tell which part of it is an SQL clause and which is a parameter. They’re just chars in a string! Bear with me here:
SELECT * FROM book_table WHERE BOOK_ID = :id
Now, imagine passing 123456 as the parameter. Nothing special.
But because the parameter can be of many possible types, and no check is intrinsically required, I can pass also a string and hope it would work.
Since passing strings is allowed, I could even pass the parameter followed by another SQL clause and don’t break anything as long as the final string is still a syntactically valid SQL statement!
Imagine passing “12345 AND 1=1” . The query would result in:
SELECT * FROM book_table WHERE BOOK_ID = 12345 AND 1=1
Which will have the same result as before since 1=1 is tautologically true. It seems useless but it may be used to enhance a query:
SELECT * FROM book_table WHERE BOOK_ID = 12345 OR BOOK_TITLE LIKE ‘The%’
By passing 12345 OR BOOK_TITLE LIKE ‘The%’ as the parameter, we would get all books having that Id or having a title starting with the word “the”. I let you imagine all the creative and clever ways to exploit this!
Malicious SQL Injection
Not everyone out there is just looking for enhancements. Imagine that the parameter is taken by the user input, and imagine this scenario:
SELECT * FROM user_credentials WHERE USER_ID = :id
Nothing too different from before.
But the malicious user passes the parameter abcd OR 1=1 , resulting in:
SELECT * FROM user_credentials WHERE USER_ID = abcd OR 1=1
Well, USER_ID = abcd OR 1=1 is always true, since 1=1 is always tautologically true. This means that the WHERE condition is not filtering out any result and all the table content is returned.
What if it contains sensitive data?
How to Prevent the SQL Injection?
From my point of view, there is only one way.
The same thing you do if you want to prevent an idiot user to bring your program down by just passing nonsense input:
defensive programming.
Defensive Programming is a coding practice that relies on behaviour prediction in order to reduce the number of bugs or problems of other nature. This is especially needed when unexpected user inputs or actions may lead to failures or vulnerabilities, like in the case of an SQL injection.
So, before passing the input parameter and feeding it to the “query builder”, it should pass a check, that can be customized and finely tailored around the use case.
For example, to prevent this:
SELECT * FROM user_credentials WHERE USER_ID = abcd OR 1=1
the parameter should represent an Id and one or more of these criteria can be applied:
it should be numeric
it should have a fixed-length
it should not contain forbidden chars (like blank spaces)
it should not contain SQL keywords wrapped in blank spaces
Et cetera.
https://www.hihonor.com/latam/club/topicdetail/topicid-71445/
https://www.hihonor.com/latam/club/topicdetail/topicid-71446/
https://www.hihonor.com/latam/club/topicdetail/topicid-71448/
https://www.hihonor.com/latam/club/topicdetail/topicid-71450/
https://www.hihonor.com/latam/club/topicdetail/topicid-71451/
https://www.hihonor.com/latam/club/topicdetail/topicid-71452/
https://www.raceofchampions.com/profile/the-roundup-full-version-online-free/profile
https://www.raceofchampions.com/profile/ko-the-roundup-full-1080p-fox/profile
https://www.raceofchampions.com/profile/doctor-strange-2-online-4k-free-f-o-x/profile
https://www.raceofchampions.com/profile/dr-strange-watch-free-streaming-at-home/profile
https://www.hihonor.com/latam/club/topicdetail/topicid-71456/
https://www.hihonor.com/latam/club/topicdetail/topicid-71457/
https://www.hihonor.com/latam/club/topicdetail/topicid-71458/
https://www.hihonor.com/latam/club/topicdetail/topicid-71460/
https://www.hihonor.com/latam/club/topicdetail/topicid-71463/
https://www.hihonor.com/latam/club/topicdetail/topicid-71464/
https://www.hihonor.com/latam/club/topicdetail/topicid-71465/
https://www.hihonor.com/latam/club/topicdetail/topicid-71468/
https://pastelink.net/5vinoo2w
https://pasteio.com/xbRsfyfiL10r
https://ide.geeksforgeeks.org/bae2e66d-e8e8-4e2c-9424-a721c847268a
https://paiza.io/projects/gBp8kKHn20dTSZvYKO6cHw
https://paste.tbee-clan.de/tMyDP
https://m.mydigoo.com/forums-topicdetail-462686.html
https://mypaper.pchome.com.tw/dkhull/post/1381114933
https://godselectpeople.ning.com/forum/topics/wemweirywe
https://zenodo.org/communities/kekrjdfhdher
http://allabouturanch.com/forum/topics/weiojnsdlfsd
https://public.flourish.studio/visualisation/10108230/
Check this to see how I put code in this article:
3 Ways to Insert Code in Medium Articles
PLUS a fresh NEW one!
medium.com
If you liked the article, please clap to it and share it!
Also, take a look at my games!
Get access to my stories and those of other Medium writers for just $5 a month. With no additional cost to you, I will receive half of your payments as a commission: it’s a great way to support me!
