SQL Requêtes préparées

Partagez votre expérience de PureBasic avec les autres utilisateurs.
Avatar de l’utilisateur
falsam
Messages : 7244
Inscription : dim. 22/août/2010 15:24
Localisation : IDF (Yvelines)
Contact :

SQL Requêtes préparées

Message 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)
Configuration : Windows 11 Famille 64-bit - PB 6.03 x64 - AMD Ryzen 7 - 16 GO RAM
Vidéo NVIDIA GeForce GTX 1650 Ti - Résolution 1920x1080 - Mise à l'échelle 125%
Avatar de l’utilisateur
falsam
Messages : 7244
Inscription : dim. 22/août/2010 15:24
Localisation : IDF (Yvelines)
Contact :

Re: SQL Requêtes préparées

Message 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
Configuration : Windows 11 Famille 64-bit - PB 6.03 x64 - AMD Ryzen 7 - 16 GO RAM
Vidéo NVIDIA GeForce GTX 1650 Ti - Résolution 1920x1080 - Mise à l'échelle 125%
Marc56
Messages : 2146
Inscription : sam. 08/févr./2014 15:19

Re: SQL Requêtes préparées

Message 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:
Marc56
Messages : 2146
Inscription : sam. 08/févr./2014 15:19

Re: SQL Requêtes préparées

Message 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?
Marc56
Messages : 2146
Inscription : sam. 08/févr./2014 15:19

Re: SQL Requêtes préparées

Message 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)
Avatar de l’utilisateur
falsam
Messages : 7244
Inscription : dim. 22/août/2010 15:24
Localisation : IDF (Yvelines)
Contact :

Re: SQL Requêtes préparées

Message 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)
Configuration : Windows 11 Famille 64-bit - PB 6.03 x64 - AMD Ryzen 7 - 16 GO RAM
Vidéo NVIDIA GeForce GTX 1650 Ti - Résolution 1920x1080 - Mise à l'échelle 125%
Avatar de l’utilisateur
Philippe_GEORGES
Messages : 112
Inscription : mer. 28/janv./2009 13:28

Re: SQL Requêtes préparées

Message 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
Philippe GEORGES
"La simplicité est la sophistication suprême" (De Vinci)
assistance informatique, création de logiciels
georges.informatique@gmail.com
Avatar de l’utilisateur
djes
Messages : 4252
Inscription : ven. 11/févr./2005 17:34
Localisation : Arras, France

Re: SQL Requêtes préparées

Message par djes »

Excellent tuto, comme d'hab, falsam :)
Avatar de l’utilisateur
microdevweb
Messages : 1798
Inscription : mer. 29/juin/2011 14:11
Localisation : Belgique

Re: SQL Requêtes préparées

Message 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.
Windows 10 64 bits PB: 5.70 ; 5.72 LST
Work at Centre Spatial de Liège
Répondre