Prepared Statements Everywhere?

Databases
1

I’m in the process of converting all of my user input mySQL queries to prepared statements to prevent SQL attacks. The remainder of my queries are without direct user input and are requests the app sends to the cloud database itself usually triggered by a timer. Is there any real benefit in converting the remaining non user input queries to prepared statements? Any advice? Thanks!

2

without params it really wont matter one way or the other and a prepared statement is just extra code for you to write :slight_smile:

3

@Norman Palardy Well the system creates params but they are not being typed in by the user so I just wondered if there was any security benefit as it is a lot of code for me to change for sure… Thanks!

4

Well in that case IF you have params I’d use a prepared statement
It just makes it less likely that you can accidentally do something bad :slight_smile:
And it also makes dealing with things that may have quotes or other special chars in them much easier

5

@Norman Palardy Thank you sir, if it has params I’ll convert them.

6

As a rule of thumb, if I control all the input, and there is literally no chance of any of it being bad (only numbers and no strings, for example), I just use straight SQL.

7

I go one step farther… I use an ORM 99% of the time so the ORM does all the heavy lifting. And the one I use, uses PreparedStatements so I know I (should be) am good.

8

Hey guys how do I handle NOW() in a prepared statement?

ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)

MYSQL_TYPE_TIME or MYSQL_TYPE_TIMESTAMP

Do I then write it like?

rs = ps.SQLSelect(NOW())
9

Also I can’t really find an example of how to do a Insert/Update with a prepared statement. Any examples around?

Having trouble with this bit. Can you guys tell me how to fix it? Thanks.

[code]
Dim db As New MySQLCommunityServer
Dim ps As PreparedSQLStatement

If db.Connect Then
  Dim sqlTime As String
  Dim strOffset As String
  
  strOffset = GetOffset
  
  sqlTime = "SET time_zone = '" + strOffset + "';"
  
  db.SQLExecute(sqlTime)
  
  db.Prepare("INSERT INTO User (strID, strName, strEmail, strPass, strCldID, strDateAdded) "+_
  "VALUES(?, ?, ?, ?, ?, ?, ?);"
  
  ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
  ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_STRING)
  ps.BindType(2, MySQLPreparedStatement.MYSQL_TYPE_STRING)
  ps.BindType(3, MySQLPreparedStatement.MYSQL_TYPE_STRING)
  ps.BindType(4, MySQLPreparedStatement.MYSQL_TYPE_STRING)
  ps.BindType(5, MySQLPreparedStatement.MYSQL_TYPE_TIMESTAMP)
  
  ps.SQLExecute(strID, strName, strEmail, strPass, strCldID, NOW())
  
Else
  // Connection error
End If
db.Close[/code]
11

You’ve set the bind types, but also need to set the bind values.

12

SQL goes into the SQL statement.

db.Prepare("INSERT INTO User (strID, strName, strEmail, strPass, strCldID, strDateAdded) "+_ "VALUES(?, ?, ?, ?, ?, ?, NOW());"

13

They look exactly like the non-prepared statements except you replace the variable bits with a “?”.

14

[quote=261449:@Tim Parnell]SQL goes into the SQL statement.

db.Prepare("INSERT INTO User (strID, strName, strEmail, strPass, strCldID, strDateAdded) "+_ "VALUES(?, ?, ?, ?, ?, ?, NOW());"[/quote]
This was it, I was adding the NOW() to the execute like a param. Thanks.

15

You could have also used new Date as the last value, but that’s FYI only. Tim*2’s suggestion is better in this case.

16

The only difference would be if the client & the server are running in different locales & timezones (ie ones in GMT and one local time)
new Date would give you client based time & date
NOW() will give you the server based ones

17

@Norman Palardy That’s exactly what I wanted the server based times.