mysql ez simplification

[tsue]

hello, im trying to simplify this code

Case $button10


            Local $sMySqlStatement = ""
            $aOk = _EzMySql_GetTable2d("SELECT * FROM " & GUICtrlRead($Input2) & ";")
            Local $rows = UBound($aOk)
            If GUICtrlRead($Input12) > $rows Then
                MsgBox(0, "error", "exede rows bd: " & $rows & "leido: " & GUICtrlRead($Input12) )
            Else
                $aOk = _EzMySql_GetTable2d("SELECT * FROM " & GUICtrlRead($Input2) & " WHERE RowID = " & "'" & GUICtrlRead($Input12) & "';")
                $error = @error
                If Not IsArray($aOk) Then
                    MsgBox(0, $sMySqlStatement & " error", $error)
                Else
                    GUICtrlSetData($Input10, $aOk[1][1])
                    GUICtrlSetData($Input11, $aOk[1][2])
                    GUICtrlSetData($Input12, $aOk[1][3])
                EndIf
            EndIf

            Local $rows = UBound($aOk)
            Local $cols = UBound($aOk, 2)
            Local $dims = UBound($aOk, 0)
            MsgBox(0, "The " & $dims & "-dimensional array has", _
                    $rows & " rows, " & $cols & " columns")
            _ArrayDisplay($aOk, "2d array")

so i dont have to use too much code in the error reporting, i want it to look something like this,

Case $button10
            
            If Not _EzMySql_GetTable2d("SELECT * FROM " & GUICtrlRead($Input2) & " WHERE RowID = " & "'" & GUICtrlRead($Input12) & "';") Then
                MsgBox(0, "Error inserting data to Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
                
            EndIf

but this keeps getting me errors

[jchd]

Few remarks:

• Which error(s) do you get and where?
• In your second code box, SELECT failing will display a misleading "Error inserting data..."
• I'm no regular MySQL user but I suspect that "... where rowid = '123456' ... " isn't going to do what you think it should. IFAIK rowid is an integer but you're comparing it to a string.

 

Other generic SQL remarks:

• SELECT * from ... is a kind of gamble over the future. Should you have to reorganize your schema and change column order, then your application will fall flat on its nose. Don't be lazy and enumerate columns explicitely wanted.
• Sanitize or, at the very least, escape user input. Maybe you'll be the only user of this code but in a more general setup, expect the user input to be potentially malicious or simply containing a single quote!

[tsue]

 

Few remarks:

• Which error(s) do you get and where?
• In your second code box, SELECT failing will display a misleading "Error inserting data..."
• I'm no regular MySQL user but I suspect that "... where rowid = '123456' ... " isn't going to do what you think it should. IFAIK rowid is an integer but you're comparing it to a string.

 

Other generic SQL remarks:

• SELECT * from ... is a kind of gamble over the future. Should you have to reorganize your schema and change column order, then your application will fall flat on its nose. Don't be lazy and enumerate columns explicitely wanted.
• Sanitize or, at the very least, escape user input. Maybe you'll be the only user of this code but in a more general setup, expect the user input to be potentially malicious or simply containing a single quote!

 

mmm i have this problem: when an error occurs the program crashes and it exit, is there a way yust to return the error like msgbox(0, "error", "somthing"), and avoid exiting(crashing) the program

thanks for your other tips i still dont understand them very well but ill keep learning 

[jchd]

By just looking at a small exerpt of the actual code and _EzMySql_* functions (UDF?) not being my playground, it's difficult to tell what causes the crashes you experience.

Regarding select * from... : * will return columns in the order where the DDL (the create table, then possibly alter table statements) put them. If a change in the column order happens after your application is widespread, the column retrieved won't match the program expectations anymore. Retrieving columns explicitely overcomes this potential pitfall.

About unescaped literals, the problem here is the well-known SQL injection attack:

Your SQL code: "update customers set address = '" & $variable1 & "' where customerID = " & $rowid & ";"

Malvolent user input for $variable1: "'; drop table customers; drop table items; drop table invoices; drop table suppliers; -- fuck you!"

Edited July 5, 2013 by jchd