PureBasic

Forums PureBasic - Français
https://www.purebasic.fr/french/

SQL Requêtes préparées

https://www.purebasic.fr/french/viewtopic.php?t=16597

Page 1 sur 1

SQL Requêtes préparées

Publié : jeu. 23/mars/2017 12:33
par falsam
Je pense que 100% des requêtes (y compris les miennes) de mises à jour de bases de données que j'ai pu voir sur ce forum sont vulnérables.

■ Exemples partiels de requêtes vulnérables..

Code : Tout sélectionner

psSQLRequest = "UPDATE contacts SET "
  psSQLRequest + "contact_Numero_Client='"       +GetGadgetText(#String_0)+"', "
  psSQLRequest + "contact_Numero_Facture='"      +GetGadgetText(#String_1)+"', "
  psSQLRequest + "contact_Numero_Contrat='"      +GetGadgetText(#String_2)+"', "
  psSQLRequest + "contact_prenom='"              +GetGadgetText(#String_3)+"', "
ou

Code : Tout sélectionner

;Préparation de la requete de création 
ReqSql = "insert into contacts (nom, prenom, age) values ("
ReqSql + Chr(34) + nom + Chr(34)+Chr(44)
ReqSql + Chr(34) + note + Chr(34)+Chr(44)
ReqSql + Chr(34) + age + Chr(34)+")"
:!: Si vos données comportent des apostrophes ou des guillemets, il ne sera pas possible de mettre à jour votre bases de données.

■ la solution : Les requêtes préparées.
Une requête préparée consiste à remplacer les variables encadrées par des guillemets ou des apostrophes par des points interrogation.

Reprenons le dernier exemple de requête vulnérable.
:idea: Chr(34) + nom + Chr(34) sera remplacé par un simple ?

■ Mise en oeuvre avec un exemple d'insertion d'enregistrement.

Code : Tout sélectionner

ReqSql = "insert into contacts (name, note, age) values (?,?,?)"
le premier ? aura l'index 0 le second ? aura l'index 1 et le troisième ? aura l'index 2

Et enfin, il faut maintenant mettre à jour ces indexs et appliquer la mise à jour.

Code : Tout sélectionner

SetDatabaseString(Database, 0, GetGadgetText(#Name)) 
SetDatabaseString(Database, 1, GetGadgetText(#Note))
SetDatabaseString(Database, 2, GetGadgetText(#Age))

DatabaseUpdate(Database, ReqSql)
En rapport avec ce sujet.
- Créer et mettre à jour une base de données Sqlite (Une refonte est en cours)

Re: SQL Requêtes préparées

Publié : jeu. 23/mars/2017 12:37
par falsam
Un exemple complet avec des requêtes préparées de :
- Insertion d'un enregistrement,
- Modification d'un enregistrement,
- Suppression d'un enregistrement.

■ Création de la base de données.

Code : Tout sélectionner

UseSQLiteDatabase()

Define.i Database 
Define.s DatabaseName.s = "mabase.sqlite", ReqSql.s=""  

;Creation d'un fichier vide
Database = CreateFile(#PB_Any, DatabaseName)

If Database
  CloseFile(Database) 
Else
  MessageRequester("Erreur", "Impossible de créer un fichier")
  End
EndIf

;Creation de la table contacts
Database = OpenDatabase(#PB_Any, DatabaseName, "", "", #PB_Database_SQLite)
If Database   
  ReqSql = "CREATE TABLE contacts (" 
  
  ReqSql + "Idauto INTEGER PRIMARY KEY,"
  ReqSql + "name TEXT,"
  ReqSql + "note TEXT," 
  ReqSql + "age INTEGER"
  
  ReqSql + ")"
  
  DatabaseUpdate(Database, ReqSql)
  
  If DatabaseError() <> ""
    MessageRequester("Information", "Impossible de créer la table 'contacts'")
  EndIf  
EndIf
■ Code de mise à jour de la base de données.

Code : Tout sélectionner

EnableExplicit

Enumeration Window
  #mf
EndEnumeration

Enumeration Gadget
  #mfList
  
  #mfName
  #mfNote
  #mfAge
  
  #mfNew
  #mfUpdate
  #mfDelete
EndEnumeration

UseSQLiteDatabase()

Global Database, DatabaseName.s = "mabase.sqlite", ReqSql.s = ""  

Structure newRecord 
  Item.i    
  Idauto.i
  Name.s
  Note.s
  Age.s
EndStructure
Global Record.newRecord

;Plan de l'application
Declare Start()
Declare ShowRecords()
Declare RecordSelect()
Declare RecordNew()
Declare RecordUpdate()
Declare RecordDelete()
Declare Exit()

Start()

Procedure Start()
  Database = OpenDatabase(#PB_Any, DatabaseName, "", "", #PB_Database_SQLite)
  If Not Database
    MessageRequester("Information", "Impossible s'ouvrir la base de données")
    Exit()
  EndIf
  
  OpenWindow(#mf, 0, 0, 800, 600, "Contacts", #PB_Window_SystemMenu|#PB_Window_ScreenCentered)
  ListIconGadget(#mfList, 5, 10, 250, 570, "Nom", 240, #PB_ListIcon_FullRowSelect|#PB_ListIcon_AlwaysShowSelection)
  
  TextGadget(#PB_Any, 275, 26, 80, 20, "Nom")
  StringGadget(#mfName, 365, 23, 400, 20, "")
  
  TextGadget(#PB_Any, 275, 55, 80, 20, "Note")
  EditorGadget(#mfNote, 365, 55, 400, 175)
  
  TextGadget(#PB_Any, 275, 245, 80, 20, "Age")
  StringGadget(#mfAge, 365, 245, 400, 20, "", #PB_String_Numeric)
  
  ButtonGadget(#mfNew,700,480,80,24,"Nouveau")
  ButtonGadget(#mfUpdate,700,515,80,24,"Insert")
  ButtonGadget(#mfDelete,700,550,80,24,"Supprimer")
  
  ;Triggers
  BindGadgetEvent(#mfList, @RecordSelect(), #PB_EventType_LeftClick)
  BindGadgetEvent(#mfNew, @RecordNew())
  BindGadgetEvent(#mfUpdate, @RecordUpdate())
  BindGadgetEvent(#mfDelete, @RecordDelete())
  
  BindEvent(#PB_Event_CloseWindow, @Exit()) 
  
  ShowRecords()
  
  Repeat : WaitWindowEvent() : ForEver
EndProcedure

;Liste des enregistrements
Procedure ShowRecords()
  ReqSql = "select idauto, name, note from contacts"
  
  If DatabaseQuery(Database, ReqSql)
    ClearGadgetItems(#mfList)
    While NextDatabaseRow(Database)
      AddGadgetItem(#mfList, -1, GetDatabaseString(Database, 1))
      SetGadgetItemData(#mfList, CountGadgetItems(#mfList) - 1, GetDatabaseLong(Database, 0)) 
    Wend
  EndIf  
  SetGadgetState(#mfList, Record\Item)
  SetActiveGadget(#mfList)
  RecordSelect()
EndProcedure

;Affichage d'un contact
Procedure RecordSelect()
  Record\Item   = GetGadgetState(#mfList)
  Record\Idauto = GetGadgetItemData(#mfList, Record\Item)
  
  ReqSql = "select name, note, age from contacts where idauto = ?"
  SetDatabaseLong(Database, 0, Record\Idauto)
  If DatabaseQuery(Database, ReqSql) And NextDatabaseRow(Database)
    SetGadgetText(#mfName, GetDatabaseString(Database, 0))
    SetGadgetText(#mfNote, GetDatabaseString(Database, 1))
    SetGadgetText(#mfAge, GetDatabaseString(Database, 2))
  EndIf 
  
  SetGadgetText(#mfUpdate, "Mise à jour")
  DisableGadget(#mfUpdate, #False)
  DisableGadget(#mfDelete, #False)
EndProcedure

;Reset du formulaire de saisie de contact
Procedure RecordNew()
  Protected Gadget
  
  ClearStructure(Record, newRecord)
  For Gadget = #mfName To #mfAge
    SetGadgetText(Gadget, "") 
  Next
  SetGadgetText(#mfUpdate, "Insert")
  DisableGadget(#mfUpdate, #False)
  DisableGadget(#mfDelete, #True)
  SetActiveGadget(#mfName)
EndProcedure

;Mise à jour du contact
Procedure RecordUpdate()
  
  ;Préparation des requetes de création et de mise à jour d'un enregistrement
  If Record\Idauto = 0
    ReqSql = "insert into contacts (name, note, age) values (?,?,?)"
    Record\Item = CountGadgetItems(#mfList) 
  Else
    ReqSql = "update contacts set name = ?, note = ?, age = ? where idauto = ?"  
  EndIf
  
  ;Controle commun à la création & la modification de données 
  ;   - Le nom est obligatoire
  With Record
    \Name     = GetGadgetText(#mfName)
    \Note     = GetGadgetText(#mfNote)
    \Age      = GetGadgetText(#mfAge)
    
    If \Name <> ""
      SetDatabaseString(Database, 0, \Name) 
      SetDatabaseString(Database, 1, \Note)
      SetDatabaseString(Database, 2, \Age)
      SetDatabaseLong(Database, 3, \Idauto)
      
      DatabaseUpdate(Database, ReqSql)
      
      If DatabaseError() <> ""
        MessageRequester("Information", "Immpossible de mettre à jour la base de données" + #CRLF$ + DatabaseError())
      EndIf
      ShowRecords()
    Else
      MessageRequester("Information", "Le nom est obligatoire")
      SetActiveGadget(#mfName)
    EndIf
  EndWith
EndProcedure

Procedure RecordDelete()
  ReqSql = "delete from contacts where idauto = ?" 
  SetDatabaseLong(Database, 0, Record\Idauto)
  DatabaseUpdate(Database, ReqSQL)
  If DatabaseError() <> ""
    MessageRequester("Information", "Immpossible de mettre à jour la base de données" + #CRLF$ + DatabaseError())
  EndIf
  ShowRecords()
EndProcedure

Procedure Exit()  
  If IsDatabase(Database)
    CloseDatabase(Database)
  EndIf
  End
EndProcedure

Re: SQL Requêtes préparées

Publié : jeu. 23/mars/2017 17:58
par Marc56
Intéressant effectivement et très utile pour travailler avec ces ""
J'ai eut du mal à comprendre, même en relisant plusieurs fois la doc
C'est quand même étonnant ce (?) transformé une chaine.

« Adresses des labels : '?'
Il peut également être utile de connaître l'adresse d'un label dans votre programme. Cela peut être le cas pour accéder au code ou aux données placées à cet endroit ou toute autre bonne raison qui peut vous venir à l'esprit. Pour trouver l'adresse d'un label dans votre programme, placez un '?' devant le nom du label. »

Merci Falsam
(encore un truc nouveau appris aujourd'hui)
:wink:

Re: SQL Requêtes préparées

Publié : ven. 24/mars/2017 7:01
par Marc56
:idea: Je pense qu'il serait plus clair d'inverser les deux derniers exemples de ton didacticiel :wink:
(affecter, puis utiliser) C'est écrit, mais plus difficile à comprendre tel que tu l'as mis (à mon avis)

■ Mise en œuvre avec un exemple d'insertion d'enregistrement.

Commencer par affecter les valeurs aux indexes:

Code : Tout sélectionner

SetDatabaseString(Database, 0, GetGadgetText(#Name))
SetDatabaseString(Database, 1, GetGadgetText(#Note))
SetDatabaseString(Database, 2, GetGadgetText(#Age))
Ainsi le premier ? aura l'index 0 le second ? aura l'index 1 et le troisième ? aura l'index 2

:arrow: Ce qui permet d'écrire ensuite:

Code : Tout sélectionner

ReqSql = "insert into contacts (name, note, age) values (?,?,?)"

DatabaseUpdate(Database, ReqSql)
Et voilà, fini les Chr(34) et possibilité d'intégrer " et ' :P

:arrow: Voir aussi pour les les autres type de données:
SetDatabaseLong(), SetDatabaseQuad(), SetDatabaseFloat(), SetDatabaseDouble() SetDatabaseBlob()

:!: Ce qui est dommage c'est que cette syntaxe ne marche pas avec LIKE (%?% ne fonctionne pas) :|

:wink:
Bon, j'ai quelques programmes à modifier, et ça va me simplifier la vie. Merci 8)


PS.
:idea: Peut-être un jour un Wiki PureBasic ? pour entretenir (à plusieurs) toutes les docs, didacticiels, trucs, liens etc répartis un peu partout et pas ou plus maintenus au bout d'un certain temps?

Re: SQL Requêtes préparées

Publié : ven. 24/mars/2017 10:58
par Marc56
Marc56 a écrit : :!: Ce qui est dommage c'est que cette syntaxe ne marche pas avec LIKE (%?% ne fonctionne pas) :|
J'ai trouvé (c'est tout simple) il faut encapsuler le % dans la chaine dont on passe l'adresse
et non pas dans la chaine SQL comme j'avais fait :roll:

Code : Tout sélectionner

Enumeration 
     #DB
EndEnumeration

UseSQLiteDatabase()
OpenDatabase(#DB, GetCurrentDirectory() + "mabase.sqlite", "", "", #PB_Database_SQLite)

; --- Requête normale
Text$ = "AAA"
SetDatabaseString(#DB, 0, Text$)
DatabaseQuery(#DB, "SELECT * FROM contacts WHERE name = ?")

While NextDatabaseRow(#DB) 
     Debug "Trouvé: " + GetDatabaseString(#DB, 1)
Wend
FinishDatabaseQuery(#DB)


; -------------------------
Debug "--- Requête LIKE"

Text$ = "AA%" ; <--- ici le(s) %
SetDatabaseString(#DB, 0, Text$)
DatabaseQuery(#DB, "SELECT * FROM contacts WHERE name LIKE ?")
; et pas
; DatabaseQuery(#DB, "SELECT * FROM contacts WHERE name LIKE %?%")

While NextDatabaseRow(#DB) 
     Debug "Trouvé: " + GetDatabaseString(#DB, 1)
Wend
FinishDatabaseQuery(#DB)
8)

Re: SQL Requêtes préparées

Publié : ven. 24/mars/2017 11:12
par falsam
Je pense qu'il serait plus clair d'inverser les deux derniers exemples de ton didacticiel :wink:
(affecter, puis utiliser)
Dans cet exemple et surtout avec PureBasic j'aurais pu inverser. J'ai appris la technique des requêtes préparées avec le langage Php avant de connaitre PureBasic. En Php on prépare la requête et on affecte ensuite. L'inverse n'est pas possible.

Maintenant on va passer à ce qui t’intéresse.
Ce qui est dommage c'est que cette syntaxe ne marche pas avec LIKE (%?% ne fonctionne pas)
avec le langage SQL on aurait pu utiliser CONCAT() dans un SELECT : CONCAT('%','?','%') pour former qu'une seule chaine. Ca fonctionne avec MySql mais pas avec SQLite.

je suis allé faire un tour dans la doc SQLite à la recherche d'une fonction ou d'un opérateur de concaténation. et YEahHHHhhh ça existe.
The || operator is "concatenate"
https://sqlite.org/lang_expr.html

■ Mise en oeuvre.

Code : Tout sélectionner

  ReqSql = "select idauto, name, note from contacts where name like '%'||?||'%'"
  
  SetDatabaseString(database, 0, valeurquetusouhaites)

Re: SQL Requêtes préparées

Publié : lun. 17/avr./2017 17:27
par Philippe_GEORGES
Merci pour ce code !!
Je cherchai un moyen simple et rapide d'utiliser une base de données, là je suis comblé !

Un très grand merci !

Phil

Re: SQL Requêtes préparées

Publié : mer. 29/août/2018 6:50
par djes
Excellent tuto, comme d'hab, falsam :)

Re: SQL Requêtes préparées

Publié : mer. 29/août/2018 6:59
par microdevweb
Merci falsam pour ces précieuses informations, la je retrouve un peux le même système que JDBCSqlite de Java.

Je m'étonne d'être passé à côté de ces fonctions qui existe je viens de regardé depuis la 5.40, comme quoi on ne lit jamais assez la doc.
Fuseau horaire sur UTC+01:00
Page 1 sur 1

Développé par phpBB® Forum Software © phpBB Limited

Traduction française officielle © Qiaeru