Prepare to prevent sql-injection
I recently watched this OWASP Top-10 talk from Jim Manico and tried this sample using basic golang. I am not a security expert, but by doing this I understood how parameterized queries would keep us safe from basic level
SQL-injection attacks. And also how golang’s go-sql-driver automatically make decisions based on our parameterized queries.
For this I had used golang with docker and docker-compose, Wireshark to monitor packets and MySQL.
First lets see how non-parameterized queries would behave with
badEmail := "'email@example.com'"results, err := db.Query("SELECT password FROM users where email=" + badEmail)
’sac’or’1'!=’@email.com’ this is a legit email address. When I run this I got following in Wireshark
docker-compose log, I got following
2020/08/31 14:50:34 awesomepassword
2020/08/31 14:50:34 awesomepassword2
These are users password saved in MySQL database. Hacker can easily get what they want with these kind of sql attacks.
Now lets see how parameterized queries would prevent this attack in basic level.
In golang with go-sql-driver, you just have to put
? in SQL statement.
badEmail := "'firstname.lastname@example.org'"results, err := db.Query("SELECT password FROM users where email=?", badEmail)
When I run this, I get following output in Wireshark.
And I get nothing in response, not readable characters. With this
prepare statement, we have prevented simple SQL-injection in our code.
Golang will automatically changed to a parametrized query and run
execute in two different requests to DBMS. This possible because go-sql-driver’s
interpolateParams is set to
false by default. In this mode
driver has to prepare a statement, execute it with given parameters and close the statement again
Now lets see how
parameterized-quires saved us from SQL-injection. When we use parameterized-queries, DBMS knows what we really want to do.
SELECT password FROM users where email=? with this, DBMS knows you are gonna give an email address as input and DBMS needs to send password to that respective email address. And next it will receive
sac’or’1'!=’@email.com’ as email address. It will just check for an email address syntax equals to this given string.
prepare is like, we tell DBMS to be prepared for a specific thing. As an example, we say
prepare to accept a cricket ball so DBMS is ready to accept a cricket ball. If it get a football, it will reject this football request.
Also this gives performance improvement to our DBMS. Specially
prepare statement will be optimized and compiled only once by DBMS. I got to know these after reading this.
As I have said earlier, I am not an security expert, just checked these stuffs as I got curious. Also this reddit question gave me basic understanding about how these things worked in golang. If I had made a mistake, please let me know.