Thursday, October 27, 2011

Safely connecting to a MySQL Database

You can connect to a MySQL database in a Real Studio web or desktop application using the MySQLCommunityServer class. If you have built a web application you need to consider SQL injection which is a way for someone to get data out of your database that you didn't want them to have or change data that you didn't want changed. The goal is to make sure that user input (such as SQL commands or delimiters) doesn't contain anything to manipulate a database in an unwanted way.

So, before you upload your web app, take a look at using prepared statements to protect your data. Although it uses the Real SQL Database there is example code for this in the Order example project. You can download that project from the about box in the Orders example.

Here's some example code from the Real Studio Language Reference:

// We'll assume "db" is the MySQL Database object added to your project
Dim stmt As MySQLPreparedStatement
stmt = MySQLPreparedStatement(db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?"))
stmt.Bind(0, "John")
stmt.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
stmt.Bind(1, 20)
stmt.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_SHORT)

Dim rs As RecordSet = stmt.SQLSelect


Paul said...

I suspect the last line should be:

stmt.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_SHORT)

jason@real said...

Nice catch Paul! Thanks. This has been fixed.