Share
SQL est un autre langage essentiel pour les développeurs souhaitant créer des sites Web basés sur les données. Cependant, de nombreux développeurs ne connaissent pas divers aspects de SQL. dans cet article, nous analyserons dix astuces essentielles.
Les développeurs Web ont souvent une pléthore de langues à leur disposition. Il est essentiel que les développeurs utilisent le langage approprié pour le travail..
Passons en revue le code suivant. Dans le premier exemple, le développeur sélectionne toutes les colonnes et toutes les lignes de la table customer. Dans le deuxième exemple, le développeur sélectionne uniquement le prénom, le nom et l'adresse dans la table customer pour un seul client portant l'ID 1001. Non seulement la seconde requête limite les colonnes renvoyées, mais elle fonctionne également mieux..
SELECT * FROM client;
SELECT nom, nom, shippingAddress FROM client WHERE customerID = 1001;
Lorsque vous écrivez du code, assurez-vous qu'il fonctionne efficacement.
Trop de développeurs sont satisfaits d'un code qui fonctionne correctement sur 100 lignes de données, sans trop réfléchir au moment où la base de données comportera 10 000 lignes..
Les bases de données stockent des informations précieuses. De ce fait, les bases de données sont souvent les principales cibles des attaques. De nombreux développeurs ne savent pas que leur code comporte des vulnérabilités de sécurité critiques, ce qui est très inquiétant, non seulement pour les clients, mais également pour vous. Actuellement, les développeurs peuvent être tenus légalement responsables si leur négligence personnelle entraîne un risque de sécurité de la base de données qui est ensuite exploité..
Si vous n'êtes pas convaincu de la gravité de la sécurité des bases de données, ces deux articles devraient vous aider à mieux comprendre.
"Le FBI et la police de l'État de Virginie sont à la recherche de pirates informatiques qui ont exigé que l'État leur paye une rançon de 10 millions de dollars d'ici jeudi pour le retour de millions de dossiers pharmaceutiques personnels qu'ils ont volés dans la base de données de médicaments d'ordonnance de l'État."
Lire l'article du Washington Post"Kaspersky Lab, une entreprise de sécurité basée à Moscou, a reconnu aujourd'hui qu'une base de données contenant des informations sur les clients avait été exposée pendant près de 11 jours et qu'elle n'avait été mise au courant que lorsque des pirates roumains en ont informé l'entreprise samedi."
Lire l'article de ComputerWorld
Passons en revue un autre exemple utilisant le pseudo-code.
Accentsconagua
// Code théorique txtUserName.setText ("eshafer 'OR 1 = 1"); query = "SELECT nom d'utilisateur, mot de passe FROM utilisateurs WHERE nom_utilisateur = '" + txtUserName.getText () + "';"; // Final statement query = "SELECT nom d'utilisateur, mot de passe DES utilisateurs WHERE nom d'utilisateur = ejshafer OR 1 = 1;" J'espère que vous avez examiné le code ci-dessus et remarqué la vulnérabilité. La requête finira par sélectionner tous les enregistrements de nom d'utilisateur et de mot de passe de la table, car 1 est toujours égal à 1. Cet exemple particulier n'a pas beaucoup d'effet pour le pirate potentiel. Cependant, il existe des possibilités presque illimitées de code malveillant supplémentaire pouvant être ajouté avec des résultats catastrophiques..
La solution est souvent spécifique à un SGBD. c'est-à-dire qu'il varie entre MySQL, Oracle et SQL Server. En PHP avec MySQL, par exemple, il est habituel d'échapper des paramètres en utilisant la fonction mysql_real_escape_string avant d'envoyer la requête SQL. Vous pouvez également utiliser des instructions préparées pour "préparer" vos requêtes. Donnez-vous pour mission de comprendre le SGBD avec lequel vous travaillez et les problèmes de sécurité inhérents.
L'injection SQL n'est pas la seule vulnérabilité de sécurité des bases de données et des développeurs, mais il s'agit de l'une des méthodes d'attaque les plus courantes. Il est important de tester votre code et de vous familiariser avec les derniers problèmes de sécurité liés à votre SGBD afin de vous protéger contre les attaques..
Les instructions de sélection SQL à table unique sont plutôt faciles à écrire. Cependant, les exigences de l'entreprise dictent souvent que des requêtes plus complexes doivent être écrites. Par exemple, "recherchez toutes les commandes pour chaque client et affichez les produits pour chaque commande". Maintenant, dans cette situation particulière, il y aurait probablement une table client, une table d'ordre et une table order_line (la dernière serait de résoudre une possible relation d'enregistrement plusieurs-à-plusieurs). Pour ceux qui sont un peu plus familiarisés avec SQL, il est évident qu'une jointure de table nécessite en fait deux jointures de table pour cette requête. Regardons un exemple de code.
SELECT client.customerID, order.order_id, order_line.order_item FROM client INNER JOIN commande order ON client.customerID = commande.customerID INNER JOIN commande_ligne ON order.orderID = commande_ligne.orderID;
Bon, assez simple. Pour ceux qui ne le savent pas, le code ci-dessus est une jointure interne. Plus spécifiquement, le code ci-dessus est une équi-jointure.
Définissons les différents types de jointures.
Jointures internes: le but fondamental des jointures internes est de renvoyer les enregistrements correspondants..
Jointures externes: les jointures externes ne nécessitent pas que chaque enregistrement ait un enregistrement correspondant.
Un merci spécial à Ronald Erdei pour les images.
Il existe un dernier type de jointure qui doit être pris en compte, à savoir une jointure automatique. Une auto-jointure est simplement une jointure d'une table à elle-même.
EMPLOYEE TABLE -EmployeeName -SupervisorID
Dans cette situation, pour déterminer quels employés sont supervisés par un employé donné, une auto-adhésion serait requise..
Espérons que cela clarifie les principes de base des jointures, car il s’agit de l’une des fonctionnalités principales de SQL qui en fait un langage de base de données aussi puissant. Assurez-vous que vous utilisez la jointure appropriée à votre situation.
En SQL, chaque colonne de table a généralement un type de données associé. Texte, Entier, VarChar, Date, etc. sont généralement des types disponibles pour les développeurs..
Lors du développement, assurez-vous de choisir le type de données approprié pour la colonne. Les dates doivent être des variables DATE, les nombres doivent être de type numérique, etc. Cela devient particulièrement important lorsque nous traitons d'un sujet ultérieur: l'indexation; mais je vais vous montrer un exemple de mauvaise connaissance des types de données ci-dessous:
SELECT employeeID, employeeName FROM employee WHERE employeeID = 112457891;
Cela semble bien basé sur ce que nous savons actuellement, correct? Cependant, que se passe-t-il si employeeID est en réalité une chaîne? Nous avons maintenant un problème, car le SGBD peut ne pas trouver de correspondance (parce que les types de données chaîne et les entiers sont de types différents).
Par conséquent, si vous utilisez l'indexation, vous serez probablement perplexe quant aux raisons pour lesquelles votre requête prend une éternité, alors qu'il s'agira d'une simple analyse d'index. C'est la raison pour laquelle les développeurs doivent accorder une attention particulière aux types de données et à leurs applications. Les attributs non-clés qui sont des ID sont souvent des types de chaîne, par opposition à des entiers, en raison de la flexibilité accrue accordée. Cependant, il s’agit également d’un problème pour les développeurs débutants, qui supposent que les champs d’identification seront des entiers..
L'utilisation correcte des types de données est essentielle à la programmation correcte de la base de données, car elle conduit directement à l'efficacité des requêtes. Des requêtes efficaces sont essentielles pour créer des applications évolutives de qualité.
Tous les langages de programmation ont des normes que les développeurs Web doivent connaître, et SQL n’est pas différent. SQL a été normalisé par ANSI puis ISO, avec de nouvelles révisions du langage soumises occasionnellement. La dernière révision est SQL: 2008, bien que la révision la plus importante à connaître par les développeurs est SQL: 1999. La révision de 1999 comportait des requêtes récursives, des déclencheurs, la prise en charge de PL / SQL et T-SQL, ainsi que quelques nouvelles fonctionnalités. Il a également défini que les instructions JOIN doivent être effectuées dans la clause FROM, par opposition à la clause WHERE..
Lors de la rédaction d'un code, il est important de garder à l'esprit pourquoi un code conforme aux normes est utile. Il existe deux raisons principales pour lesquelles les normes sont utilisées. Le premier est la facilité de maintenance et le second est la normalisation inter-plateformes. Comme pour les applications de bureau, il est supposé que les sites Web auront une longue durée de vie et subiront diverses mises à jour pour ajouter de nouvelles fonctionnalités et résoudre les problèmes. Comme tout analyste de systèmes vous le dira, les systèmes passent la majorité de leur durée de vie en phase de maintenance. Lorsqu'un autre programmeur aura accès à votre code dans 2, 5 ou 10 ans, pourra-t-il toujours comprendre ce que votre code fait? Les normes et les commentaires sont conçus pour promouvoir la maintenabilité.
L'autre raison est la fonctionnalité multi-plateforme. Avec CSS, il existe actuellement une bataille en matière de normes entre Firefox, Internet Explorer, Chrome et d'autres navigateurs sur l'interprétation du code. La raison des normes SQL est d'éviter une situation similaire entre Oracle, Microsoft et d'autres variantes SQL telles que MySQL..
La normalisation des bases de données est une technique permettant d’organiser le contenu des bases de données. Sans normalisation, les systèmes de base de données peuvent être inexacts, lents et inefficaces. La communauté des professionnels de la base de données a élaboré une série de directives pour la normalisation des bases de données. Chaque «niveau» de normalisation est désigné sous le nom de formulaire et il existe 5 formulaires au total. La première forme normale correspond au niveau de normalisation le plus bas, jusqu’à la cinquième forme normale, soit le niveau de normalisation le plus élevé.
Dans la réalité du développement de la base de données, atteindre le point 3NF est le saut le plus important. 4NF et 5NF sont un peu plus un luxe (et parfois une nuisance) dans le développement de bases de données, et sont rarement vus dans la pratique. Si vous vous débattez avec les concepts ou si vous vous souvenez des trois premières formes, il existe une relation simple. "La clé, la clé entière et rien que la clé.", Qui concerne 1NF, 2NF et 3NF.
Maintenant, sans aller trop loin dans la théorie des bases de données, concentrons-nous simplement sur les avantages de la normalisation. Au fur et à mesure que les données progressent dans les formulaires de normalisation, elles deviennent plus propres, mieux organisées et plus rapides. Maintenant, avec une petite base de données qui ne contient que 5 tables et 100 lignes de données, cela ne sera pas évident. Cependant, à mesure que la base de données grandira, les effets de la normalisation deviendront beaucoup plus évidents en ce qui concerne la rapidité et le maintien de l'intégrité des données. Cependant, dans certaines situations, la normalisation n'a pas de sens, par exemple lorsque la normalisation des données crée des requêtes excessivement complexes, nécessaires pour renvoyer les données.
Maintenant, il s’agit d’un point généralement ignoré; En fait, tous les exemples de code que j'ai présentés dans ce tutoriel ont essentiellement enfreint cette astuce. En termes de développement de base de données, un nom d'objet qualifié complet se présente comme suit: DATABASE.schema.TABLE. Voyons maintenant pourquoi les noms qualifiés complets sont importants et dans quelles situations ils sont nécessaires. Le but d'un nom d'objet qualifié complet est d'éliminer l'ambiguïté. Les développeurs débutants ont rarement accès à plusieurs bases de données et schémas, ce qui complique les problèmes à l'avenir. Lorsqu'un utilisateur donné a accès à plusieurs bases de données, plusieurs schémas et les tables qui s'y trouvent, il devient crucial de spécifier directement à quoi l'utilisateur tente d'accéder. Si vous avez une table d'employés, votre supérieur dispose d'une table d'employés et le schéma sur lequel votre application Web s'exécute possède une table d'employés, à laquelle vous essayez réellement d'accéder.?
Logiquement, le nom complet ressemblerait à DATABASE.SCHEMA.OBJECTNAME, cependant, syntaxiquement (c'est-à-dire dans les instructions exécutables), il s'agirait simplement de SCHEMA.OBJECTNAME. Bien que divers SGBD présentent différentes différences de syntaxe, le style ci-dessus est généralement applicable.
-- Pas "SELECT * FROM table" SELECT * FROM schema.TABLE
La qualification complète de vos noms de base de données est importante lorsque vous travaillez avec des bases de données plus volumineuses, utilisées par plusieurs utilisateurs et contenant plusieurs schémas. Cependant, c’est une bonne habitude de prendre.
Un index de base de données est une structure de données qui accélère les opérations sur une table de base de données. Les index peuvent être créés en utilisant une ou plusieurs colonnes d'une table de base de données, fournissant à la fois une base pour des recherches aléatoires rapides et un accès efficace aux enregistrements ordonnés. L'indexation est extrêmement importante lorsque vous travaillez avec des tables volumineuses. Cependant, des tables parfois plus petites doivent être indexées si elles sont supposées croître. Les petites tables qui resteront petites ne doivent cependant pas être indexées (par exemple, si votre livre compte 1 page, est-il judicieux de se tourner vers l'index?)
De nombreux développeurs écrivent leur code et le testent sur une table de 10 ou 100 lignes. Ils sont satisfaits lorsque leur code fonctionne correctement. Toutefois, lorsque la table atteint 10 000 ou 1 000 000 lignes, le code ralentit au rythme d'un escargot et le client peut tout aussi bien aller déjeuner en attendant que le code soit exécuté..
Lorsqu'une requête recherche dans une base de données un enregistrement correspondant, la recherche peut être effectuée de deux manières..
Il est important de se rappeler que les index doivent être reconstruits à l'occasion, à mesure que des données sont ajoutées à la table. De plus, bien que les index améliorent les performances d'accès aux données, cela ralentit la modification des données. De ce fait, la plupart des SGBD ont une option permettant de désactiver temporairement un index afin de faciliter la modification en masse de données, puis d'autoriser sa réactivation et sa reconstruction ultérieure..
Lorsque vous travaillez avec une base de données comportant plusieurs utilisateurs, il est important de gérer correctement les différentes autorisations de base de données. Bien entendu, la plupart des bases de données ont un utilisateur administrateur, mais est-il toujours judicieux d’exécuter vos requêtes en tant qu’administrateur? De plus, voudriez-vous fournir à tous vos développeurs et utilisateurs débutants vos informations d'identification d'administrateur pour pouvoir écrire leurs requêtes? Préférablement pas. Les différentes autorisations possibles pour votre base de données dépendent de votre SGBD, mais il existe des thèmes communs entre elles..
Dans MySQL, par exemple, tapez "SHOW TABLES" pour afficher une liste des tables de votre base de données, dont vous remarquerez probablement une table "utilisateur". En tapant «utilisateur DESC», vous découvrirez qu'il existe différents champs dans la table des utilisateurs. Outre l'hôte, le nom d'utilisateur et le mot de passe, il existe également une liste de privilèges pouvant être définis pour un utilisateur. En outre, une table 'db' régit davantage de privilèges pour une base de données spécifique..
SQL Server fournit les instructions GRANT, DENY et REVOKE pour donner ou retirer des autorisations à un utilisateur ou à un rôle. De plus, SQL Server fournit des rôles tels que db_writer, db_reader. Souvent, des développeurs inconnus attribuent ces rôles (par opposition à la création de leurs propres rôles personnalisés) à d'autres utilisateurs, ce qui entraîne une sécurité globale réduite de la base de données et la possibilité qu'un utilisateur effectue une opération non désirée..
La gestion correcte des autorisations des utilisateurs de votre base de données est essentielle pour gérer non seulement la sécurité, mais également pour permettre un développement plus rapide et la protection de l'intégrité des données..
Les bases de données sont des outils puissants, mais elles ne sont pas sans limites. Oracle, SQL Server et MySQL ont tous des limitations uniques sur des éléments tels que la taille maximale de la base de données, le nombre maximal de tables et autres. Sans le savoir, de nombreux développeurs choisissent une solution de SGBD pour leur projet sans planifier ni prendre en compte les exigences ultérieures de leur base de données..
Reportez-vous au manuel de votre SGBD pour connaître les différentes limitations. Par exemple, les limitations de SQL Server se trouvent sur le site Web MSDN: http://msdn.microsoft.com/en-us/library/ms143432.aspx
Dans cet article, nous avons examiné 10 astuces essentielles pour les développeurs SQL. Cependant, il existe de nombreuses autres techniques SQL utiles qui pourraient être mentionnées. laissez donc vos pensées dans les commentaires, que vous pensiez que cet article couvrait tous les sujets essentiels, ou que vous en pensiez un. Continuez à développer et rappelez-vous que le code que vous écrivez prend en charge l'infrastructure Internet. Sans vous, Internet n'aurait pas autant de succès..
