Prepare to prevent sql-injection

https://4.bp.blogspot.com/-tVq3J_mLDnc/WoZ_D_Vg0nI/AAAAAAAAA74/TBkjVN24qPo7Q7NlbjnJZ09-dje60LKaQCLcBGAs/s1600/sqlinjection2.png

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

Unsafe Select request
Unsafe query response

And in 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 := "'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.

Prepare statement request
Execute statement

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.

Ref-1, Ref-2

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…

--

--

--

DevOps who is willing to learn and try new things.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Scrape an online newspaper and display the hot topics in a Word Cloud -Python

Mobile Banking Application Development: Features & Cost

A Lesson That Matters

Being a Perfect in Quality Assurance.

ServiceMesh Installation Types

Software

Valuable Software Integrations to know in 2022

Some Common LeetCode Problems For Interviews

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Sachith Muhandiram

Sachith Muhandiram

DevOps who is willing to learn and try new things.

More from Medium

Rate-limiting with Token Buckets

Would You Like to Create a New Blockchain From the Beginning?

Hyperledger Explorer setup using Docker

Consortium blockchain solution on Hyperledger Fabric with 3 organizations