syntax sql insert mysql problem

[mLipok]

Navicat ... nice tool.
I'will buy I hope this year, but for now I have other products on my shopping list .

 

[faustf]

yea  is  simply  and   friendly    i like so much ,  i think mysql client   by oracle  is  just to much complicated  but  i think  after 2 o3  step  is  much better , but  for me is perfect

 

 

[faustf]

what you use? 

[mLipok]

MS SQL Management Studio
and
MySQL Workbench
and
SQLite Explorer

and of course AutoIt+ADO.au3 UDF

 

 

[faustf]

o good  you are  a  pro

 

[faustf]

i  have  little problem   with insert and update  

i have  apart of script like  this  

expandcollapse popup

If $CC_ANAG = 1 Then
        Local $sOPA_ragion_social = "'" & GUICtrlRead($ANAC_Input1) & "', "
        Local $sOPA_Cognome = "'" & GUICtrlRead($ANAC_Input2) & "', "
        Local $sOPA_via = "'" & GUICtrlRead($ANAC_Input3) & "', "
        Local $sOPA_citta = "'" & GUICtrlRead($ANAC_Input4) & "', "
        Local $sOPA_provincia = "'" & GUICtrlRead($ANAC_Input5) & "', "
        Local $sOPA_stato = "'" & GUICtrlRead($ANAC_Input6) & "', "
        Local $sOPA_mail = "'" & GUICtrlRead($ANAC_Input23) & "', "
        Local $sOPA_cell = "'" & GUICtrlRead($ANAC_Input10) & "', "
        Local $sOPA_piva = "'" & GUICtrlRead($ANAC_Input7) & "', "
        Local $sOPA_codfi = "'" & GUICtrlRead($ANAC_Input8) & "', "
        Local $sOPA_tel = "'" & GUICtrlRead($ANAC_Input9) & "', "
        Local $sOPA_cap = "'" & GUICtrlRead($ANAC_Input11) & "'"

        Local $sSLA_ragion_social = "'" & GUICtrlRead($ANAC_Input12) & "', "
        Local $sSLA_Cognome = "'" & GUICtrlRead($ANAC_Input13) & "', "
        Local $sSLA_via = "'" & GUICtrlRead($ANAC_Input14) & "', "
        Local $sSLA_citta = "'" & GUICtrlRead($ANAC_Input15) & "', "
        Local $sSLA_provincia = "'" & GUICtrlRead($ANAC_Input16) & "', "
        Local $sSLA_stato = "'" & GUICtrlRead($ANAC_Input17) & "', "
        Local $sSLA_mail = "'" & GUICtrlRead($ANAC_Input24) & "', "
        Local $sSLA_cell = "'" & GUICtrlRead($ANAC_Input21) & "', "
        Local $sSLA_piva = "'" & GUICtrlRead($ANAC_Input18) & "', "
        Local $sSLA_codfi = "'" & GUICtrlRead($ANAC_Input19) & "', "
        Local $sSLA_tel = "'" & GUICtrlRead($ANAC_Input20) & "', "
        Local $sSLA_cap = "'" & GUICtrlRead($ANAC_Input22) & "'"

        If $ANAC_Control_click_carica = 0 Then
            Local $syntax = "INSERT INTO ge_anagrafica_clienti (sop_ragion_social,sop_cognome,sop_via,sop_citta,sop_prov,sop_stato,sop_mail,sop_cell,sop_piva,sop_codfi,sop_tel,sop_cap,sl_ragion_social,sl_cognome,sl_via,sl_citta,sl_prov,sl_stato,sl_mail,sl_cell,sl_piva,sl_codfi,sl_tel,sl_cap)" & _
                    " VALUES (" & $sOPA_ragion_social & $sOPA_Cognome & $sOPA_via & $sOPA_citta & $sOPA_provincia & $sOPA_stato & $sOPA_mail & $sOPA_cell & $sOPA_piva & $sOPA_codfi & $sOPA_tel & $sOPA_cap & $sSLA_ragion_social & $sSLA_Cognome & $sSLA_via & $sSLA_citta & $sSLA_provincia & $sSLA_stato & $sSLA_mail & $sSLA_cell & $sSLA_piva & $sSLA_codfi & $sSLA_tel & $sSLA_cap & ")"

            Local $queryde = _Mysql_Query($syntax)
            If @error Then
                SetError(@error, @extended, $ADO_RET_FAILURE)
            EndIf
        Else
            Local $syntax = "UPDATE ge_anagrafica_clienti SET sop_ragion_social=" & $sOPA_ragion_social & _
                "sop_cognome=" & $sOPA_Cognome & _
                "sop_via=" & $sOPA_via & _
                "sop_citta=" & $sOPA_citta & _
                "sop_prov=" & $sOPA_provincia & _
                "sop_stato=" & $sOPA_stato & _
                "sop_mail=" & $sOPA_mail & _
                "sop_cell=" & $sOPA_cell & _
                "sop_piva=" & $sOPA_piva & _
                "sop_codfi=" & $sOPA_codfi & _
                "sop_tel=" & $sOPA_tel & _
                "sop_cap=" & $sOPA_cap & _
                "sl_ragion_social=" & $sSLA_ragion_social & _
                "sl_cognome=" & $sSLA_Cognome & _
                "sl_via=" & $sSLA_via & _
                "sl_citta=" & $sSLA_citta & _
                "sl_prov=" & $sSLA_provincia & _
                "sl_stato=" & $sSLA_stato & _
                "sl_mail=" & $sSLA_mail & _
                "sl_cell=" & $sSLA_cell & _
                "sl_piva=" & $sSLA_piva & _
                "sl_codfi=" & $sSLA_codfi & _
                "sl_tel=" & $sSLA_tel & _
                "sl_cap=" & $sSLA_cap & _
                "WHERE id=" & $ANAC_Control_click_carica

            Local $queryde = _Mysql_Query($syntax)
            If @error Then
                SetError(@error, @extended, $ADO_RET_FAILURE)
            EndIf
        EndIf
    EndIf
    GUICtrlDelete($ANAC_List1)
    _listwiev_anagrafica_clienti()

when  work a  update  give me this  error 

###############################
ADO.au3 v.2.1.13 BETA (1155) : ==> COM Error intercepted !
$oADO_Error.description is:     [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.28]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sl_ragion_social='stefano', sl_cognome='cerbioni', sl_via='Fucecchio', sl_citta=' at line 1
$oADO_Error.windescription:     Exception occurred.

$oADO_Error.number is:     80020009
$oADO_Error.lastdllerror is:     0
$oADO_Error.scriptline is:     1155
$oADO_Error.source is:     Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:     
$oADO_Error.helpcontext is:     0
###############################
 

and  when  work insert  have  this  

 

###############################
ADO.au3 v.2.1.13 BETA (1155) : ==> COM Error intercepted !
$oADO_Error.description is:     [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.28]Column count doesn't match value count at row 1
$oADO_Error.windescription:     Exception occurred.

$oADO_Error.number is:     80020009
$oADO_Error.lastdllerror is:     0
$oADO_Error.scriptline is:     1155
$oADO_Error.source is:     Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:     
$oADO_Error.helpcontext is:     0
###############################
+>00:41:30 AutoIt3.exe ended.rc:0
+>00:41:30 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 19.07

 

 

[jchd]

You should always escape possible single quotes in text values. To see why, look what the following statement looks like when it's received by the engine:

Local $name = "O'Connor"
Local $sql = "insert into mytable (name) values ('" & $name & "');"
ConsoleWrite($sql & @LF)
; compare to this
$sql =  "insert into mytable (name) values ('" & SQLesc($name) & "');"
ConsoleWrite($sql & @LF)

Func SQLesc($s)
    Return StringReplace($s, "'", "''")
EndFunc

This can cause the first error you cite.

The second error kind can easily be simulated:

Local $name = "O','Connor"
Local $sql = "insert into mytable (name) values ('" & $name & "');"
ConsoleWrite($sql & @LF)
; compare to this
$sql =  "insert into mytable (name) values ('" & SQLesc($name) & "');"
ConsoleWrite($sql & @LF)

Func SQLesc($s)
    Return StringReplace($s, "'", "''")
EndFunc

Not escaping string carefully opens the door to suprious runtime errors as shown above and, more worrisome SQL injection attacks .

[faustf]

i did do a  simply   error   i  write  this  

  Local $sOPA_cap = "'" & GUICtrlRead($ANAC_Input11) & "'"

and  i must  write   this

  Local $sOPA_cap = "'" & GUICtrlRead($ANAC_Input11) & "',"

thankz  at  all

 

[jchd]

Oops, I typed a note on this missing comma but it got deleted somehow under my feet when I inserted the rest of my post.

So yes you had a missing comma in this case, yet you still aren't safe by not escaping strings as the rest of my post demonstrates.

[faustf]

i  dont  understund  so much  your  example  

 

i understund   the problem of  sql injection  but   is  better  the  first  or  the  second  ?? 

 

exmple : 

insert into mytable (name) values ('O','Connor');
insert into mytable (name) values ('O'',''Connor');

i  use  what book  sql  tell , i think

 

[jchd]

The first line is invalid SQL: due to the string viciously containing  O','Connor the values part ends up being now two values. Imagine if the user types the following instead: OConnor');drop table mytable;--

Then the SQL statement would be turned into not less than 3 valid statements, blue, red and green:

insert into mytable (name) values ('OConnor');drop table mytable;--');

The only safe way is to escape all single quotes (double them) in input strings, just like my example SQLesc() function. Always!