INFO: Vários exemplos de SQL

[Rafael.Andrade 0]

>>> Vários COUNT numa mesma SELECT !=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=

 

=> Ótimo para criação de estatísticas.

 

  SELECT COUNT(CASE WHEN mt.status = 1 THEN 1 ELSE NULL END) AS "Aguardando",
        COUNT(CASE WHEN mt.status = 2 THEN 1 ELSE NULL END) AS "Aberta",
        COUNT(CASE WHEN mt.status = 3 THEN 1 ELSE NULL END) AS "EmManutencao",
        COUNT(CASE WHEN mt.status = 4 THEN 1 ELSE NULL END) AS "Fechada",
        COUNT(CASE WHEN mt.status = 5 THEN 1 ELSE NULL END) AS "Cancelada",
        COUNT(*) AS "Total"
        FROM minha_tabela mt;

!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!!=!=!=!=!=!=!=!=!

 

 

>>> CRIPTOGRAFANDO SENHAS !=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=

 

Função: AES_ENCRYPT([sTRING_QUE_SERÁ_ENCRIPTADA],[PALAVRA_CHAVE])

 

Ao gravar senhas no banco de dados é necessário que você criptografe para que niguém consiga obter livre acesso ao seu sistema através de injeção SQL realizando um SELECT simples.

 v_senha = AES_ENCRYPT(v_senha,"SuperChave");
 INSERT INTO tbl_usuarios(Email,Senha) VALUES(v_email,v_senha);

Para descriptografar use o comando AES_DECRYPT([sTRING_QUE_SERÁ_DECRIPTADA],[PALAVRA_CHAVE])

SELECT Email,AES_DECRYPT(Senha,"SuperChave") as "Senha" FROM tbl_usuarios;

Lembrando que a palavra chave é CASE SENSITIVE.

Obs.: AES_ENCRYPT possui criptografia de 128 bits e pode ser extendido a até 256 bits.

Para mais funções de criptografia consulte: "Encryption Functions"

!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!!=!=!=!=!=!=!=!=!

 

 

 

>>> PROTEÇÃO CONTRA SQL INJECTION E RECOMENDAÇÕES DE SEGURANÇA !=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=

 

Para evitar SQL Injection é bom usar todos recursos possíveis, veja algumas atitudes de Segurança.

 

- Use somente PROCEDURES para se comunicar com o BD, benefícios:

> Você poderá analisar todos dados antes de executar uma SELECT;

> Dificulta injeção de SQL;

> Pode tratar os erros exibindo mensagens personalisadas;

> Pode usar IF's e outros comandos;

> Economiza digitação no sistema;

> SQL ficarão padronizadas;

> Se precisar alterar uma SQL é só alterar na Procedure que todo seu sistema ficará atualizado ao invés

de caçar cada comando no seu sistema.

> Entre outros...

 

- Crie um usuário no seu MySQL com permissão de apenas ler PROCEDURES e use ele no sistema, deste modo você forçará usar somente as

PROCEDURES que você criou, evitando assim que um Hacker execute comandos como DELETE FROM, DROP TABLE, DROP SCHEMA, UPDATE, SELECT ...

No MySQL existe o banco "information schema" que dá todas informações de todos bancos cadastrados (inclusive tabelas e campos), usando

um usuário limitado o Hacker não conseguirá ver os bancos, tabelas, campos e nem conseguirá executar comandos SQL simples.

 

- Em procedures de Login faça sempre a verificação, filtrando palavras chave de SQL (É necessário também filtrar no Sistema (ASP,PHP...)).

 

- Use Session ao invés de Cookies pois é muito mais seguro.

 

- Tome muito cuidado com a utilização de QueryString(método que passa informações através da URL), só passe aquilo que os

mecanismos de buscas podem indexar e aquilo que os usuários podem ver. QueryString é muito útil quando se trata de link, pois assim

uma pessoa pode apontar para sua página diretamente.

 

- Nunca faça exclusão física(DELETE FROM) pois além de ser um problema de segurança não há chance de recuperar.

Crie um campo em cada tabela (Ex.: Deletado [bOOLEAN]) e somente altere o valor de FALSE para TRUE.

 

- Crie uma tabela de histórico, que grave o usuário que fez a alteração e qual alteração que foi feita e em que data e hora. Crie uma TRIGGER

para cada tabela para alimentar esta tabela de histórico automaticamente quando houver uma alteração, assim você vai ter o controle de quem está fazendo o que

e até saberá se algum Hacker está usando usuários do seu sistema.

!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!!=!=!=!=!=!=!=!=!

 

 

>>> INNER JOIN, LEFT JOIN, RIGHT JOIN e Outros... !=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=

 

=> Diferenças:

]> INNER JOIN: É um relacionador de tabelas ANSI de 1992 como substituição do WHERE para relacionamento, deixando o WHERE somente como filtro, não há

diferença de performance de usar INNER JOIN vs. WHERE, mas é muito recomendado usar por questões de fácil alteração de sintaxe e facilidade de ver o que é relacionamento

de tabela e o que é filtro (coisa que usando somente WHERE não é possível).

]> LEFT JOIN: É um relacionador de tabelas que exibe tudo o que existe em 2 ou mais tabelas que estão relacionadas ao mesmo tempo e o que existe somente em uma tabela.

]> RIGHT JOIN: É o mesmo que LEFT JOIN só que a tabela que será usada como base é a direita

 

Exemplos:

 

TABELAS

CREATE TABLE tbl_usuarios(                            || CREATE TABLE tbl_livros(                              ||  CREATE TABLE tbl_livros_lidos(
 id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,     ||   id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,     ||    livroID INTEGER NOT NULL,
 Email VARCHAR(100) NOT NULL,                        ||   Nome VARCHAR(50) NOT NULL,                          ||    userID INTEGER NOT NULL,
 Senha CHAR(15) NOT NULL,                            ||   Autor VARCHAR(50) NOT NULL,                         ||    FOREIGN KEY(livroID) REFERENCES tbl_livros(id),
 NomeCompleto VARCHAR(50) NOT NULL                   ||   Ano INTEGER NOT NULL,                               ||    FOREIGN KEY(userID) REFERENCES tbl_usuarios(id),
);                                                    ||   Editora VARCHAR(50) NOT NULL                        ||    PRIMARY KEY(livroID,userID)
                                                     ||  );                                                   ||  );

 

REGISTROS:

INSERT INTO tbl_usuarios(Email,Senha,NomeCompleto) VALUES
("email1@provedor.com","321","Márcio Motta"),
("email2@provedor.com","123","Francisco da Silva"),
("email3@provedor.com","456","Beltrano Bittencourt"),
("email4@provedor.com","789","Fulano Alencar");


INSERT INTO tbl_livros(Nome,Autor,Ano,Editora) VALUES
("A Casa Verde","Mario Vargas Llosa",1966,"Desconhecida"),
("A Vaca e o Hipogrifo","Mario Quintana",1977,"Globo"),
("O Ferro e o Voto Secreto","Monteiro Lobato",1931,"Globo"),
("A Escrava Isaura","Bernardo Guimarães",2001,"Martin Claret"),
("Batuque, Samba e Macumba","Cecília Meirelles",1934,"Martins Editora"),
("Receita de Ano Novo","Carlos Drummond de Andrade",2008,"Record");

INSERT INTO tbl_livros_lidos VALUES(1,1),(2,2),(3,2),(4,1),(5,1),(6,1),(6,2),(3,3),(1,4),(4,3),(3,4);

 

Como você pode ver a tabela tbl_livros_lidos mostra quais livros cada usuário já leu.

 

 SELECT u.NomeCompleto as "Leitor",l.Nome as "Livro" FROM tbl_usuarios u INNER JOIN tbl_livros l INNER JOIN tbl_livros_lidos ll ON u.id = ll.userID and l.id = ll.livroID;

 

Que é igual a:

 

 SELECT u.NomeCompleto as "Leitor",l.Nome as "Livro" FROM tbl_usuarios u,tbl_livros l,tbl_livros_lidos ll WHERE u.id = ll.userID and l.id = ll.livroID;

 

 

!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!!=!=!=!=!=!=!=!=!

 

Em breve estarei postando mais exemplos de SQL.

 

Abraço

 

Rafael Andrade

[Bezerk 3]

Muito legal a sua iniciativa Rafael!

 

Um ponto que eu vejo que muitos tem dificuldade em SQL, inclusive eu, é o entendimento de JOINS.

Então se você puder dar uns exemplos legais e claros de união de tabelas, o seu tópico vai ser

muito bem aproveitado por nós.

[Rafael.Andrade 0]

Ótima idéia Bezerk, assim fica até mais fácil para eu propagar meus conhecimentos.

 

flw

[Rafael.Andrade 0]

--- CONTINUAÇÃO (Não foi possível editar o primeiro POST original) ---

 

>>> INNER JOIN, LEFT JOIN, RIGHT JOIN e Outros... !=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=

 

O INNER JOIN faz a relação entre as tabelas da mesma forma que o WHERE porém fica mais claro pois mostra somente as relações, se você quiser fazer um filtro usando WHERE junto com o INNER JOIN é possível.

 

SELECT u.NomeCompleto as "Leitor",l.Nome as "Livro" FROM tbl_usuarios u INNER JOIN tbl_livros l INNER JOIN tbl_livros_lidos ll ON u.id = ll.userID and l.id = ll.livroID WHERE ll.UserID >= 3;

 

Só de bater o olho já dá pra ver que so quer exibir os usuários com ID >= 3 e está claro que não tem nada a ver com relação de tabelas, ou seja, é um filtro de conteúdo.

 

O LEFT JOIN (explicando melhor) faz uma relação de 2 ou mais tabelas, só que o diferencial dele é que ele mostra tudo o que está na tabela da esquerda que possuem ou não relação com a tabela da direita, aqueles que não possuem relação ele coloca os valores da tabela da direita como NULL.

 

Ex.:

insert into tbl_usuarios(Email,Senha,NomeCompleto) values("email5@provedor2.com","442","Cristiano Silveira");

SELECT * FROM tbl_usuarios u LEFT JOIN tbl_livros_lidos ll ON u.id = ll.userID;

 

Você deve ter notado que os valores da tabela tbl_livros_lidos para "Cristinao Silveira" ficaram nulos.

 

O RIGHT JOIN (tbm explicando melhor) é igual ao LEFT JOIN, só que o que muda é que ele verifica a tabela da direita da sintaxe ao invés da esquerda.

 

Ex.:

SELECT * FROM tbl_livros_lidos ll RIGHT JOIN tbl_usuarios u ON u.id = ll.userID;

 

Obs.: Repare que eu coloquei a tbl_usuarios para direita e tbl_livros_lidos para esquerda para mostrar que os dois comandos são iguais, retornou o resultado igual em ordem diferente.

 

!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!!=!=!=!=!=!=!=!=!

 

 

 

>>> STORED PROCEDURES !=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=

 

Objetivo: Executar um conjunto de códigos com apenas uma linha, podendo realizar verificações e até chamar outra procedure;

> Permite Recursividade.

 

Exemplo de Stored Procedure comentado e aproveito para mostrar as verificações necessárias ao se fazer um "Simples" Login.

 

DELIMITER $$ /* Muda o delimitador de finalização de ";" para "$$", evitando que ocorra erro ao finalizar um SELECT com ";" por exemplo */
CREATE PROCEDURE `Realizar_Login`( /* Nome da Procedure, não precisa usar "`" */
 IN v_email VARCHAR(35), /* Entrada de Email */
 IN v_senha VARCHAR(16)) /* Entrada de Senha */
Login:BEGIN /* Inicia a PROCEDURE com a TAG "Login" para poder finalizá-la a qualquer momento */
   SET v_email = LOWER(v_email); /* Converte todos caracteres para minúsculo */
   SET v_senha = LOWER(v_senha);
   SET v_email = TRIM(v_email); /* Remove todos espaços em branco */
   SET v_senha = TRIM(v_senha);
   SET v_senha = AES_ENCRYPT(v_senha,"SuperChave"); /* Criptografa a Senha usando a palavra chave "SuperChave" (no caso) */
   IF NOT v_email LIKE "%@%" THEN /* Como todo email possui o "@" então verifica se Não existe o "@", se não existir ele exibe a mensagem "ERROR1" e abandona a procedure (LEAVE Login) */
      SELECT "ERROR1" as "Message"; /* Se o Email não possuir algum das palavras chaves acima retorna esta mensagem */
      LEAVE Login; /* Abandona procedure. */
   END IF;
   /* Abaixo verifica se o email possui algum caracter chave de injeção SQL, se houver sai da procedure, é necessário realizar mais testes para ver se o TRIM (lá em cima) resolve o problema de injeção SQL, se resolver não precisa dessa verificação. */
   IF v_email LIKE "%\'%" or v_email LIKE "%--%" or v_email LIKE "%;%" or v_email LIKE "%=%" or
      v_email LIKE "%xp_%" or v_email LIKE "%select%" or v_email LIKE "%update%" or
      v_email LIKE "%delete%" or v_email LIKE "%drop%" or v_email LIKE "%table%" or v_email LIKE "%truncate%"
   THEN
      SELECT "ERROR2" as "Message"; /* Se o Email possuir algum das palavras chaves acima retorna esta mensagem */
      LEAVE Login;
   END IF;
   /* Como a senha está sendo criptografada, não é necessário realizar verificação pois a estrutura é alterada completamente. */
   SELECT Email,AES_DECRYPT(Senha,"SuperChave") as "Senha" FROM TBL_USUARIOS WHERE Ativo="S" AND Senha=v_senha AND Email=v_email;
END $$
DELIMITER ; /* Restaura o delimitador Padrão */

 

Executando uma Stored Procedure: CALL [NOME_DA_PROCEDURE]([PARAMETRO_ENTRADA1],[PARAMETRO_ENTRADA2],[PARAMETRO_ENTRADA3],...);

Se não houver parâmetros de entrada, fica assim: CALL [NOME_DA_PROCEDURE]();

!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!!=!=!=!=!=!=!=!=!