Como incluir caracteres especiais e valores NULL nas consultas MySQL

Usar aspas simples ou duplas, bem como outros caracteres especiais, em queries MySQL pode trazer alguma dor de cabeça para iniciantes.
Neste texto, vou procurar jogar alguma luz sobre este assunto e ajudar a resolvê-lo de forma definitiva.
Se achar interessante, inclua esta página nos seus favoritos, para futuras consultas — uma vez que o problema não é dos que incomodam com tanta frequência.
Capa do tutorial PHP - a função include e require

O problema dos caracteres especiais nas queries MySQL

Nos vários exemplos, usados nos artigos deste site, tenho evitado, quando possível, as “saias justas” com caracteres especiais.
Veja a seguinte query INSERT:

INSERT INTO perfil (nome,data_nasc,cor,comida,quantidade)
VALUES('Alvarez','1973-10-12','azul','panelada',3);

A query, acima, funciona bem e há pouca possibilidade de alguém ter problemas com as suas aspas.
Contudo, se mudarmos o valor da coluna nome para “Mont’Alverne”, o problema aparece. Veja:

INSERT INTO perfil (nome,data_nasc,cor,comida,quantidade)
VALUES('Mont'Alverne','1973-10-12','azul','panelada',3);

Note que o plugin de reconhecimento de sintaxe tem dificuldade para interpretar o valor entre as aspas simples.


O interpretador “pensa” que a segunda aspa está lá para fechar a primeira. Mas trata-se apenas de um apóstrofo — o suficiente para invalidar toda esta query.
Para evitar a confusão, há duas abordagens básicas possíveis.
Na primeira, você precede a aspa adicional com outra:

INSERT INTO perfil (nome,data_nasc,cor,comida,quantidade)
VALUES('Mont''Alverne','1973-10-12','azul','panelada',3);

Na segunda, use uma barra invertida ou backslash:

INSERT INTO perfil (nome,data_nasc,cor,comida,quantidade)
VALUES('Mont\'Alverne','1973-10-12','azul','panelada',3);

Note a diferença entre estes 2 resultados e o primeiro.


Uma outra alternativa, é usar as tradicionais aspas duplas em torno do valor Mont'Alverne. Isto também funciona:

INSERT INTO perfil (nome,data_nasc,cor,comida,quantidade)
VALUES("Mont'Alverne",'1973-10-12','azul','panelada',3);

Como lidar com valores NULL no MySQL

Se você tiver intenção de armazenar dados binários, tais como imagens ou audio, tem que ter em mente que estes valores contém todo tipo de caracteres especiais (e problemáticos), além de nulls (bytes de valor zero).
Embora o valor SQL null não seja um caractere especial, requer tratamento diferenciado.
No SQL, NULL indica “sem valor” ou “valor nulo”.
Em outros artigos, neste site, os valores NULL também têm sido evitados. Hoje, vamos encarar o problema e mostrar algumas formas de lidar com ele.
Por exemplo, se você não souber a cor preferida do Senhor Mont’Alverne, não pode preencher o valor com NULL, assim:

INSERT INTO perfil (nome,data_nasc,cor,comida,quantidade)
VALUES('Mont\'Alverne','1973-10-12','NULL','panelada',3);

O valor NULL dispensa as aspas. O correto é como se vê abaixo:

INSERT INTO perfil (nome,data_nasc,cor,comida,quantidade)
VALUES('Mont\'Alverne','1973-10-12',NULL,'panelada',3);

Como tratar os caracteres especiais dentro dentro de um script PHP

Cartoon - how sql injection work
Clique para ampliar.

Os problemas, acima, podem ser resolvidos com muita naturalidade, diretamente, por que as queries foram digitadas dentro do cliente MySQL — onde você mesmo está fornecendo os dados e pode corrigir e controlar as entradas.

A necessidade de lidar apropriadamente com os caracteres especiais é especialmente importante no ambiente da web, para o qual as queries são feitas com formulários input (form input).
Você precisa estar pronto para lidar com qualquer tipo de entrada de dados feita pelo usuário, de maneira geral.
Não é incomum, usuários maliciosos digitarem informações contendo caracteres problemáticos, com o objetivo deliberado de tentar burlar o seu
sistema
.

Se você estiver usando uma variável nome para receber aquele valor, em um script, não pode ter qualquer controle sobre o que o usuário do sistema vai te fornecer.
Neste caso, é preciso criar mecanismos para prever as diversas possibilidades de entradas e manipulá-las adequadamente.
No PHP, a função addslashes(), pode ser usada para tratar as entradas e torná-las seguras para ser inseridas em suas consultas MySQL.
Adicionalmente, a função unset() pode ser usada para forçar uma variável a não ter valor algum.

A função addslashes() retorna uma string com barras invertidas antes de caracteres que precisam ser escapados. Estes caracteres são aspas simples (‘), aspas duplas (“), barra invertida (\) e o byte NULL. manual do PHP.

Desta forma, o resultado de addslashes ("Mont'Alverne"), será Mont\'Alverne — que será recebido e gravado no banco de dados;
Se você precisar exibir este valor, sem o slash (a barra invertida), pode usar a função contrária: stripslashes() — com a mesma sintaxe.

Referências:

Manual do PHP: A função addslashes().

Como se conectar ao MySQL via Telnet

O Telnet é um protocolo de redes, com múltiplas aplicabilidades. Permitir conectar a um servidor MySQL é uma delas.
Neste texto, vou mostrar como é fácil (embora não muito útil) executar uma conexão deste tipo.
imagem de conexão mysql via telnet

Como se conectar ao MySQL via telnet

Embora alguns protocolos, tais como o SMTP e o POP sejam baseados no ASCII, conectar-se o servidor MySQL não vai trazer resultados práticos.
Infelizmente, não será possível sequer fornecer alguns comandos ao servidor.
Contudo, o procedimento é útil para determinar se o servidor está no ar.
Experimente o comando abaixo, para verificar por si mesmo:

telnet localhost 3306

Se o servidor MySQL estiver no ar, uma mensagem semelhante a que segue, será exibida:

Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
[
5.5.41-0ubuntu0.14.04.1$a['O6af�~%6"G|_EzZ2)mysql_native_passwordConnection closed by foreign host.

mysql conexão telnet
Clique para ver detalhes.

Use o comando netstat, para ter um idéia de quais portas TCP estão em uso, caso a porta 3306 não funcione para vocẽ:

netstat -tln
Conexões Internet Ativas (somente servidores)
Proto Recv-Q Send-Q Endereço Local          Endereço Remoto         Estado      
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               OUÇA      
tcp        0      0 127.0.1.1:53            0.0.0.0:*               OUÇA      
tcp        0      0 127.0.0.1:631           0.0.0.0:*               OUÇA      
tcp6       0      0 ::1:631                 :::*                    OUÇA

Como executar queries MySQL de uma shell script.

É possível inserir queries MySQL dentro de scripts shell, da mesma forma que você faria com a linguagem PHP, Perl etc.
Em vez de invocar o MySQL diretamente da linha de comando do console Bash ou digitar suas queries dentro do cliente MySQL, é possível automatizar atividades rotineiras. relacionados a bancos de dados, com scripts.
pícture of a mountain and mysql logo
É comum realizar este trabalho com o uso da linguagem Perl (com a interface DBI), no Linux/UNIX. Mas, se o que você quer, precisa ser feito rapidamente e não for muito complicado, pode ser mais interessante usar shell scripts.
Ao usar o MySQL na linha de comando, é possível pós processar os resultados com outros comandos da shell, filtrar as saídas e obter resultados mais relevantes.

Exemplos de shell scripts com comandos SQL

O script que segue serve para exibir o tempo em que o seu servidor MySQL está no ar ou uptime.
O script vai executar uma query SHOW STATUS para obter o valor da variável do status que contém o uptime (em segundos).
Abra o seu editor de texto favorito e digite o código abaixo:

#! /bin/sh
# myuptime.sh - exibe o uptime total do servidor em segundos
mysql -u root -p -B -N -e "SHOW STATUS LIKE 'Uptime'"

É importante que se diga que o editor de texto, para escrever código de programas, não pode inserir formatação dentro do script. Use um editor de texto puro, como o nano, o vi, gedit, mousepad etc.


Agora, torne o arquivo script myuptime.sh executável:

chmod +x ./myuptime.sh

Feito isto, execute-o:

./myuptime.sh 

O script vai pedir a senha de root do servidor MySQL, antes de mostrar o resultado:

Enter password: 
Uptime	3180

Entenda o script linha a linha

  • #! /bin/sh — esta primeira linha é especial. Ela invoca o Bash, que irá interpretar e executar as instruções contidas neste script.
  • # myuptime.sh ... — linhas que começam com # são comentários e, portanto, não são executadas.
  • mysql -u root -p — se você já é iniciado no MySQL, sabe que esta parte da instrução inicia o cliente, faz login e pede a senha do usuário root.
  • -B — gera uma saída batch. Exibe os resultados, usando a caractere tab como separador de colunas.
  • -N — suprime o cabeçalho da coluna. Assim temos uma saída mais limpa.
  • -e "SHOW STATUS LIKE 'Uptime'" — este é o “miolo” do nosso script — onde a mágica acontece.

Experimente remover as opções -B -N, para ver o que acontece.
Altere a linha mysql -u root -p -B -N -e "SHOW STATUS LIKE 'Uptime'", no script, para mysql -u root -p -e STATUS | grep -i uptime.
Eu obtive o seguinte resultado, após esta alteração:

Uptime:			1 hour 18 min 42 sec

Leia mais

Conheça outras formas de executar queries MySQL da linha de comando e que podem ser usadas dentro de seus scripts.
Entenda melhor o comando grep, para filtrar os seus resultados e excluir informações irrelevantes.
Saiba como pegar queries do histórico do MySQL.

Como calcular a média no MySQL

A função AVG pode ser usada para obter facilmente a média entre vários valores contidos em registros, numa tabela MySQL ou de um conjunto de números qualquer.
MySQL graphics
A função AVG() (abreviatura de average, que quer dizer média, em inglês) retorna o valor médio de uma expressão, como será mostrado nos exemplos a seguir.
Vou usar uma das minhas tabelas, no decorrer deste artigo. Sugiro que você adapte os exemplos a uma das tabelas que você já tem disponíveis — ou, se preferir, clique em SHOW SOURCE, abaixo, para ver o código SQL para criar a mesma tabela usada neste exemplo.

drop table credito;
drop table creditos;
show tables;
CREATE TABLE `creditos` (
  `Nome` varchar(255) default NULL,
  `Credito` varchar(100) default NULL,
  `Soma` varchar(100) default NULL,
  `Cidade` varchar(255)
);
CREATE TABLE `creditos` (   `Nome` varchar(255) default NULL,   `Credito` varchar(100) default NULL,   `Soma` varchar(100) default NULL,   `Cidade` varchar(255) );
INSERT INTO `creditos` (`Nome`,`Credito`,`Soma`,`Cidade`) VALUES ("Cecilia Joseph","$66.93","271,38$","Port Blair"),("Germaine Gould","$78.54","235,58$","Balsas"),("Alexandra Downs","$83.07","272,70$","Aiseau-Presles"),("Diana Sanford","$64.99","281,21$","Bünyan"),("MacKenzie Larson","$22.72","291,41$","Watermaal-Bosvoorde"),("Nichole Neal","$23.75","255,21$","Jefferson City"),("Shana Roman","$92.84","261,13$","Souvret"),("Leandra Hill","$48.94","216,40$","Albisola Superiore"),("Donna Richard","$21.82","234,94$","Weiterstadt"),("Alika Diaz","$41.36","267,14$","Cañas");
INSERT INTO `creditos` (`Nome`,`Credito`,`Soma`,`Cidade`) VALUES ("Macy Hardin","$67.23","226,71$","San José de Alajuela"),("Casey Perez","$23.37","229,08$","Tarsus"),("Ina Moran","$29.36","250,98$","Stroe"),("Meghan Branch","$56.70","291,94$","Aalbeke"),("Leah Bernard","$56.82","207,59$","Schoonaarde"),("Nichole Hill","$85.30","244,87$","Campbelltown"),("Wynne Logan","$15.63","262,09$","Parramatta"),("Daryl Dudley","$61.28","257,50$","Nieuwenrode"),("Ella Hodge","$19.14","245,43$","Mobile"),("Kerry Blankenship","$36.30","280,99$","Soissons");
INSERT INTO `creditos` (`Nome`,`Credito`,`Soma`,`Cidade`) VALUES ("Natalie Alston","$31.30","263,07$","Fort William"),("Adele Lyons","$55.35","203,75$","Beho"),("Cailin Roth","$16.36","202,51$","Gouvy"),("Patience Potts","$89.12","228,54$","Giugliano in Campania"),("Rae Byers","$86.97","205,18$","Rexton"),("Sage Roman","$41.27","241,28$","San Giovanni Suergiu"),("Erin Rowland","$14.40","269,53$","Thisnes"),("Blossom Mayo","$87.47","200,66$","Henis"),("Cassady Grant","$87.17","201,90$","Great Falls"),("Sigourney Todd","$79.09","226,67$","Tirunelveli");
INSERT INTO `creditos` (`Nome`,`Credito`,`Soma`,`Cidade`) VALUES ("Liberty Hoover","$63.51","212,05$","Baddeck"),("Maris Summers","$28.66","200,54$","Pozantı"),("Olga Bonner","$21.11","230,62$","Salem"),("Anne Sellers","$62.03","235,85$","Argyle"),("Ariana Mcmahon","$58.38","298,23$","Zonhoven"),("Basia Hull","$65.82","265,65$","Fortaleza"),("Mary Garner","$61.36","290,45$","Nocciano"),("Miranda Alford","$35.28","240,45$","Schwaz"),("Barbara Richmond","$36.26","220,42$","Buggenhout"),("Carol Hopkins","$43.93","250,51$","Premeno");
INSERT INTO `creditos` (`Nome`,`Credito`,`Soma`,`Cidade`) VALUES ("Phoebe Higgins","$19.98","260,18$","Linkhout"),("Imelda Hill","$41.72","216,16$","Camerino"),("Amber Salas","$80.05","232,63$","Wigtown"),("Linda Franklin","$87.84","258,40$","Tufo"),("Blythe Medina","$16.11","238,67$","Ramsey"),("Medge Sykes","$48.58","239,32$","Gualdo Tadino"),("Nadine Rodriguez","$52.15","206,96$","Smoky Lake"),("Kiara Dillon","$37.21","268,50$","Valladolid"),("Maggy Hampton","$13.67","267,17$","Broxburn"),("Summer White","$80.39","204,95$","Glenrothes");
INSERT INTO `creditos` (`Nome`,`Credito`,`Soma`,`Cidade`) VALUES ("Zia Bradford","$24.72","205,84$","Xhoris"),("Darryl Ashley","$73.31","205,39$","Thimoon"),("Tara White","$51.96","256,72$","Wolverhampton"),("Irma Martin","$36.56","223,05$","Rocky Mountain House"),("Bethany Colon","$69.09","246,72$","Potenza"),("Constance Roberson","$75.55","212,39$","Hunstanton"),("Karen Pickett","$75.64","213,30$","Henley-on-Thames"),("Juliet Cobb","$81.82","241,09$","Lakeland County"),("Quon Mullins","$83.09","200,25$","Aalen"),("Neve Solomon","$26.91","252,82$","Gomzo-Andoumont");
INSERT INTO `creditos` (`Nome`,`Credito`,`Soma`,`Cidade`) VALUES ("Violet Sweet","$58.51","238,10$","Altamura"),("Yolanda Cox","$43.67","200,00$","Carstairs"),("Noel Wheeler","$24.74","219,21$","Jodhpur"),("Donna Rutledge","$26.24","286,87$","Wokingham"),("Hayfa Gay","$52.63","278,01$","Angleur"),("Cameran Lindsey","$54.04","204,16$","Thame"),("Madeson Mcintyre","$72.82","249,20$","Macduff"),("Michelle Quinn","$42.27","282,91$","Thame"),("Ella Guy","$40.57","254,96$","Fort Smith"),("Adrienne Dillard","$39.83","258,71$","Wepion");
INSERT INTO `creditos` (`Nome`,`Credito`,`Soma`,`Cidade`) VALUES ("Regina Dixon","$59.67","254,59$","Okigwe"),("Latifah Mcclure","$77.45","228,58$","Beauvais"),("Karly Nash","$43.79","285,98$","Orbais"),("Halla Chapman","$42.26","210,67$","Boblingen"),("Hedy Heath","$52.32","234,23$","Qu?bec City"),("Yuri Hampton","$57.66","230,96$","Bergeggi"),("Simone Deleon","$18.54","279,98$","Asbestos"),("Nola Roberson","$53.71","256,33$","Lloydminster"),("Dai Beard","$61.39","213,10$","Bothey"),("Kendall Maldonado","$50.91","218,14$","Maransart");
INSERT INTO `creditos` (`Nome`,`Credito`,`Soma`,`Cidade`) VALUES ("Clementine Hahn","$31.53","294,62$","Etroubles"),("Veronica Potts","$60.56","227,88$","Rulles"),("Zephr Haynes","$22.18","260,61$","Strathcona County"),("Veda Burks","$83.86","285,39$","Lisieux"),("Mara Frye","$77.84","279,55$","Anápolis"),("Guinevere Bolton","$40.76","291,37$","Beaumont"),("Alexis Ferguson","$37.83","247,46$","Retie"),("Indira Morse","$18.48","237,76$","Chestermere"),("Nola Gregory","$26.10","245,04$","Villafranca in Lunigiana"),("Alyssa Randolph","$15.98","289,65$","Flint");
INSERT INTO `creditos` (`Nome`,`Credito`,`Soma`,`Cidade`) VALUES ("Harriet Frye","$82.48","299,41$","Aylesbury"),("Wynne Irwin","$30.30","290,36$","Katowice"),("Zenia Snyder","$85.96","240,38$","Milestone"),("Tasha Cantrell","$34.90","283,16$","Darion"),("Simone Moses","$41.62","242,71$","Cimitile"),("Lacota Ewing","$67.76","283,86$","Burnaby"),("Catherine Duke","$31.27","262,57$","Bloomington"),("TaShya Villarreal","$42.40","290,86$","Dumfries"),("Leilani Kelley","$76.19","207,26$","Neustadt"),("Alfreda Lynch","$71.51","207,67$","Nagarcoil");
captura de tela da listagem da tabela no MySQL
Vista parcial da tabela créditos, usada nos exemplos deste artigo.

Como usar a função AVG() no MySQL

Para obter o valor médio de todos os valores contidos na coluna Soma da tabela creditos, experimente o que segue:

SELECT AVG(Soma) FROM creditos;

O resultado é exibido assim:

+-----------+
| AVG(Soma) |
+-----------+
|    245.25 |
+-----------+
1 row in set, 100 warnings (0.00 sec)

Se você não quiser que valores repetidos entrem na conta, use a opção DISTINCT:

select AVG(DISTINCT Soma) from creditos;

O MySQL incluirá apenas valores distintos entre si na conta:

+--------------------+
| AVG(DISTINCT Soma) |
+--------------------+
|  247.3181818181818 |
+--------------------+
1 row in set (0.00 sec)

Use a seção de pesquisa e busca, no canto superior direito deste site, para descobrir mais tutoriais e dicas interessantes para você!
Referências: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_avg

Como guardar o histórico dos comandos no MySQL

Você pode armazenar o histórico de todos os comandos fornecidos no cliente MySQL, bem como seus resultados, em um arquivo, para uso posterior.
A dica é útil para programadores, que desejem montar scripts em PHP, Perl, Python etc. posteriormente, com o uso das informações obtidas em uma seção de conexão ao servidor do MySQL.
Captura de tela da saída do histórico do MySQL
Ao usar o cliente MySQL, é possível ver o resultado imediato de seus comandos — o que te permite saber o que funciona ou não.
Se você está aprendendo MySQL, pode ser interessante ter uma relação de comandos referentes à sua lição — que pode ser enviado, por email ou qualquer outra forma, para ser analisado por alguém mais experiente e que possa te ajudar com alguma dificuldade.

Execute o MySQL com a opção –tee para gravar seu histórico

O MySQL, por padrão, já grava o histórico dos seus comandos. O que queremos é ter acesso a este histórico, de fora do MySQL — para ter a liberdade de fazer o que quisermos com esta informação.
Para isto, execute o MySQL com a opção --tee, conforme o exemplo abaixo:

mysql -u root -p --tee=MySQL-historico.txt

Destrinchando o comando:

  • mysql -u root -p — rotina tradicional de conexão ao banco de dados.
  • --tee=MySQL-historico.txt — indica ao tee para enviar a saída (log) para o arquivo MySQL-historico.txt. Você pode usar qualquer outro nome que quiser para este artigo.

O log gerado pelo tee descarta uma série de lixo desnecessário, deixando apenas o que interessa gravado no arquivo.

No Unix (e também no Linux), o comando tee lê da entrada padrão e envia para uma saída à sua escolha — que pode ser um arquivo.

Como ligar o logging externo de dentro do MySQL

Se você se esqueceu de desligar o logging em um arquivo externo, é possível fazer isto de dentro do próprio MySQL.
Use os comandos \T e \t para ligar e desligar, respectivamente, o logging em arquivo externo.
No exemplo, abaixo, o logging será feito no arquivo hist.txt:

mysql> \T hist.txt
Logging to file 'hist.txt'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| tutorial           |
+--------------------+
4 rows in set (0.00 sec)

mysql> \t
Outfile disabled.
mysql> 

Para ver o log, na linha de comando (fora do cliente MySQL), eu posso usar o comando cat:

cat hist.txt 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| tutorial           |
+--------------------+
4 rows in set (0.00 sec)

mysql> \t

Ou, então, posso abrir o arquivo hist.txt com o meu editor favorito.

Captura de tela do editor mousepad com o log do mysql
Clique, para ampliar.

O MySQL acrescenta os novos logs aos já existentes. Ele não sobrescreve o log anterior.
Você precisa apagar o arquivo anterior ou criar um novo, se não quiser que o próximo log seja acrescentado a alguma preexistente.
A capacidade de criar arquivos tee, foi introduzida no MySQL 3.23.28.