[MySQL] multiple fields in one query, performance, injections

Heyhey;

For SQL in my projects on both VB6 and VB.NET, I always had a setup like this;
- MySQL Server 5.0
- MySQL ODBC 3.5.1 Connector
- ADODB 2.5 [ActiveX DataObjects, COM]

My previous VB.NET class for one of my projects;

Code:

---

Public Class clsHoloDB
    Private dbConnection As ADODB.Connection
    Private dbRecordset As ADODB.Recordset
    Function openConnection(ByVal serverHost As String, ByVal serverPort As Short, ByVal serverDatabase As String, ByVal serverUsername As String, ByVal serverPassword As String) As Boolean
        Try '// Try opening connection
            dbConnection = New ADODB.Connection
            dbRecordset = New ADODB.Recordset
            dbConnection.ConnectionString = "Driver={MySQL ODBC 3.51 Driver};Server=" & serverHost & ";Port=" & serverPort & ";Database=" & serverDatabase & ";User=" & serverUsername & ";Password=" & serverPassword & ";Option=3;"
            dbConnection.Open() '// Try opening the connection
            dbRecordset.ActiveConnection = dbConnection
            Return True

        Catch ex As Exception '// Error occured!
            Console.WriteLine("[MYSQL] Epic fail at connecting, error thrown was: " & ex.Message)
            stopServer()

        End Try
    End Function
    Friend Sub closeConnection()
        On Error Resume Next '// Make it skip errors
        dbConnection.Close() '// Close the connection
    End Sub
    Sub runQuery(ByRef Query As String)
        Console.WriteLine("[MYSQL:RUN] " & Query)
        dbConnection.Execute(Query) '// Run the query on the database
    End Sub
    Function checkExists(ByRef Query As String) As Boolean
        dbRecordset.Open(Query) '// Open the recordset for this query
        If dbRecordset.EOF = False Then checkExists = True '// If there were results found (so the row exists), then return 'true'
        dbRecordset.Close() '// Close this recordset
    End Function
    Function runRead(ByRef Query As String) As String
        Console.WriteLine("[MYSQL:READ] " & Query)
        Dim queryResult As String = vbNullString
        dbRecordset.Open(Query) ''// Open the recordset for this query

        If dbRecordset.EOF = False Then '// If there was a row found matching this critecithinges
            queryResult = dbRecordset.GetString '// Get the row
            If Not (queryResult = vbNullString) Then queryResult = queryResult.Substring(0, queryResult.Length - 1) '// If the row was found, then get it's strData and output it to the function
        End If

        dbRecordset.Close() '// Close the recordset
        Return queryResult '// Return the results
    End Function
    Function runReadArray(ByRef Query As String, Optional ByVal splitVertical As Boolean = False) As String()
        Console.WriteLine("[MYSQL:READ] " & Query)
        Dim finalArray() As String = {} '// Dimension an empty array

        dbRecordset.Open(Query) '// Open the recordset for this query
        If dbRecordset.EOF = False Then '// If there was a row found matching this critecithinges
            Dim chrSplitter As Char = Convert.ToChar(9) '// Set the default splitter
            If splitVertical = True Then chrSplitter = Convert.ToChar(13) '// It requested to split vertically, set splitter to char 13
            Dim queryResult As String = dbRecordset.GetString '// Get the row
            finalArray = (queryResult.Remove(queryResult.Length - 1)).Split(chrSplitter) '// Split the row with set splitter and output
        End If

        dbRecordset.Close() '// Close the recordset for this query

        '// Return the final array, we can't return runReadArray directly because it'll be seen as a nulled array, which is invalid.
        '// That's why we keep a real empty array called finalArray, and only store stuff in it when there's a row found =]
        Return finalArray
    End Function
    Function fixChars(ByVal strData As String) As String
        Try
            Return strData.Replace("'", "\'") '// Replace the ' character (MySQL sees it as begin and end of a input string) with another character, in the database MySQL sets it to a ' ;]

        Catch
            Return vbNullString

        End Try
    End Function
End Class

---

The big pro of this was, I could get the most detailed results in one query, read on:

Code:

---

Dim mapItems() As String = HoloDB.runReadArray("SELECT x,y,z FROM items WHERE mapid = '12'", True) '// Returns an array consisting out of the x,y,z fields from all items at mapid = '12', each x,y,z row is a new string in the array. The strings in the array are like: X[chr09]Y[chr09]Z

For i = 0 to mapItems.Count - 1 '// Iterate through array
Dim curItemData() As String = mapItems(i).Split(Convert.ToChar(9)) '// Delimit the current array string on chr09 so we get the separate X,Y,Z from this row
Console.WriteLine("X: " & curItemData(0))
Console.WriteLine("Y: " & curItemData(1))
Console.WriteLine("Z: " & curItemData(2))
Next

---

That's how I could get all the X,Y,Z of all items on mapid 12, in one query!
But, it costs the performance cost of the string delimit function 'Split', which is pretty ehm, heavy.

So I wrote a new MySQL class, mainly to stop using the ADODB 2.5 stuff set for SQL, and use the System.Data.Odbc namespace.
My new one;

Code:

---

Imports System.Data.Odbc
Imports System.Collections
''' <summary>
''' Provides high speed data access to the MySQL database of Holograph Emulator. Please don't steal my [Nillus] class lol.
''' </summary>
''' <remarks></remarks>
Public Class clsHoloDB2
    Private dbConnection As OdbcConnection
#Region "Database connection management"
    ''' <summary>
    ''' Opens connection to the MySQL database with the supplied parameters, and returns a 'true' Boolean when the connection has succeeded. Requires MySQL ODBC 3.51 driver to be installed.
    ''' </summary>
    ''' <param name="dbHost">The hostname/IP address where the database server is located.</param>
    ''' <param name="dbPort">The port the database server is running on.</param>
    ''' <param name="dbName">The name of the database.</param>
    ''' <param name="dbUsername">The username for authentication with the database.</param>
    ''' <param name="dbPassword">The pasword for authentication with the database.</param>
    Friend Function openConnection(ByVal dbHost As String, ByVal dbPort As Integer, ByVal dbName As String, ByVal dbUsername As String, ByVal dbPassword As String) As Boolean
        Try '// Try opening connection
            dbConnection = New OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=" & dbHost & ";Port=" & dbPort & ";Database=" & dbName & ";User=" & dbUsername & ";Password=" & dbPassword & ";Option=3;")
            dbConnection.Open() '// Try opening the connection
            Return True

        Catch ex As Exception '// Error occured!
            Console.WriteLine("[MYSQL] Epic fail at connecting, error thrown was: " & ex.Message)
            Return False

        End Try
    End Function
    ''' <summary>
    ''' Closes connection with the MySQL database. Any errors are ignored.
    ''' </summary>
    '''
    Friend Sub closeConnection()
        On Error Resume Next
        dbConnection.Close()
    End Sub
#End Region
#Region "Database data manipulation"
    ''' <summary>
    ''' Executes a SQL statement on the database.
    ''' </summary>
    ''' <param name="Query">The SQL statement to be executed. Default SQL syntax</param>
    Friend Sub runQuery(ByRef Query As String)
        Dim dbCommand As New OdbcCommand(Query, dbConnection)
        dbCommand.ExecuteScalar()
    End Sub
#End Region
#Region "Database data retrieval"
    ''' <summary>
    ''' Performs a SQL query and returns the first selected field as string. Other fields are ignored.
    ''' </summary>
    ''' <param name="Query">The SQL query that selects a field</param>
    Friend Function runRead(ByVal Query As String) As String
        Try
            Return New OdbcCommand(Query & " LIMIT 1", dbConnection).ExecuteScalar.ToString

        Catch
            Return vbNullString

        End Try
    End Function
    ''' <summary>
    ''' Performs a SQL query and returns all vertical matching fields as a String array. Only the first supplied columname is looked for.
    ''' </summary>
    ''' <param name="Query">The SQL query that selects a column</param>
    ''' <param name="maxResults">Adds as LIMIT to the query. Using this, the array will never return more than xx fields in of the column. When maxResults is supplied as 0, then there is no max limit</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Friend Function runReadColumn(ByVal Query As String, ByVal maxResults As Integer) As String()
        If maxResults > 0 Then Query += " LIMIT " & maxResults
        Try
            Dim columnBuilder As New ArrayList
            Dim columnReader As OdbcDataReader = New OdbcCommand(Query, dbConnection).ExecuteReader()

            While columnReader.Read = True
                columnBuilder.Add(columnReader(0).ToString)
            End While
            columnReader.Close()

            Return columnBuilder.ToArray(GetType(String))

        Catch
            Return New String(-1) {}

        End Try
    End Function
    ''' <summary>
    ''' Performs a SQL query and returns the selected in the first found row as a String array. Useable for only one row.
    ''' </summary>
    ''' <param name="Query">The SQL query that selects a row and the fields to get. LIMIT 1 is added</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Friend Function runReadRow(ByVal Query As String) As String()
        Try
            Dim rowBuilder As New ArrayList
            Dim rowReader As OdbcDataReader = New OdbcCommand(Query & " LIMIT 1", dbConnection).ExecuteReader()

            While rowReader.Read()
                For i = 0 To rowReader.FieldCount - 1
                    rowBuilder.Add(rowReader.GetString(i))
                Next
            End While
            rowReader.Close()

            Return rowBuilder.ToArray(GetType(String))

        Catch
            Return New String(-1) {}

        End Try
    End Function
#End Region
#Region "Data availability checks"
    ''' <summary>
    ''' Tries to find fields matching the query. When there is at least one result, it returns True and stops.
    ''' </summary>
    ''' <param name="Query">The SQL query that contains the seeked fields and conditions. LIMIT 1 is added</param>
    Friend Function checkExists(ByVal Query As String) As Boolean
        Try
            Return New OdbcCommand(Query & " LIMIT 1", dbConnection).ExecuteReader.HasRows

        Catch
            Return False

        End Try
    End Function
#End Region
End Class

---

Forgive me the regions in this post lol, = harder to read without IDE.

Anyway, since I disliked using the Split function so much, and I've seen the work of other people who didn't worked that way, I think I'm gonna use it like this in such cases now;

Code:

---

Dim itemIDs() As String = HoloDB.runReadColumn("SELECT id FROM items WHERE mapid = '12'", 0) '// Returns a string array with all the item id's on mapid = '12' [The 0 is just part of my function for LIMIT, 0 = no limit]

For i = 0 to itemIDs.Count - 1
Dim curItemData() As String = HoloDB.runReadRow("SELECT x,y,z FROM items WHERE id = '" & itemIDs(i) & "'")
Console.WriteLine("X: " & curItemData(0))
Console.WriteLine("Y: " & curItemData(1))
Console.WriteLine("Z: " & curItemData(2))
Next

---

They both return the same results, but there's one major difference;
Method 1 costs just one MySQL query, but a Split action which is pretty performance intensive.
Method 2 costs one MySQL query for an array of the item IDs, and then +1 query on the map for each item.
What one is the most proper and performance 'low'?
Every bit of performance is crucial since it's for a game emulator. :tongue:

So, thanks if you have token the time to read it all, hope you can give me an answer. ;]
Also another question, for people who are familar with the System.Data.Odbc set, is it still used to use: 'LIMIT' in your queries when you do

Code:

---

OdbcCommand(Query, dbConnection).ExecuteScalar.ToString()

---

Since it says that it will only return the first found row, and then stop.
So is it needed to use the LIMIT query in that case?

Meh and while I'm asking about SQL now, I'm pretty good at it but sometimes I see people writing their queries with `'s in it, like SELECT `field` FROM `lol` blabla.
This is just syntax preference, or against injections?
Because when we're talking about injections, I feel unsafe in this case. [just example]

Quote:

---

SELECT * FROM maps WHERE name = '$inputname'
$inputname: ';DELETE FROM maps;SELECT * FROM users WHERE name = '

---

So when a user searches for a map in the client, for example, and as search input it enters

Quote:

---

';DELETE FROM maps;SELECT * FROM users WHERE name = '

---

Then the server will run the following query:

Quote:

---

SELECT * FROM maps WHERE name = '';DELETE FROM maps;SELECT * FROM users WHERE name = ''

---

And boom, the maps table is emptied!
How to prevent such cases?

Anyway thanks in advance at anyone who replies/criticizes parts of my code or w/e. =]

- Nils