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 SQL-injection.
badEmail := "'sac'or'1'!='@email.com'"results, err := db.Query("SELECT password FROM users where email=" + badEmail)
As ’sac’or’1'!=’@email.com’ this is a legit email address. When I run this I got following in Wireshark
And in docker-compose log, I got following
2020/08/31 14:50:34 awesomepassword
2020/08/31 14:50:34 awesomepassword2These 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 := "'sac'or'1'!='@vx.com'"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 prepare and 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.
PS : 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.
Stay safe…
