ESP32_MySQL
Optimized library for ESP32 to directly connect and execute SQL to MySQL database WITHOUT any intermediary like HTTP server.
Table of Contents
Overview
Background Of ESP32_MySQL Library
When I was doing my Final Year Project (FYP), which the title is "Cloud-based Hydroponic Plant Monitoring System", I've been thinking on "How do I store my data to my Cloud MySQL Database without using any HTTP server such as PHP script?" I tried search Google a bit, some articles wouldn't recommend to directly execute execute SQL as if more complex SQL may need more hardware resources (CPU, RAM, Flash Memory).
Until I found some libraries online and tried some of them. Some got issues such as stack buffer overflow, memory leak and crash. I found a library, which is MySQL_MariaDB_Generic library by Dr. Charles Bell and Khoi Hoang, which great to be used. So I modified and optimized it to suit my needs, and also add some new features to it.
Features
Technically, you can execute any SQL query, but here are some features that might interest you
-
Variable INSERT data
- Take my FYP for the example. I have various of data of sensors, which obviously they are not constant values, so I need to implement variables of int values to save into my database. So do you.
-
SELECT query
- I mean, your ESP32 doesn't just INSERT (write) data to the database, your ESP32 also can read/fetch the data from the database. Here's an example, that print out the result in serial monitor (on left).
-
AES-256-ECB Encryption
- Sometimes, we may need to encrypt our sensitive data before storing to the database. In real-world scenario for this library, a project that comes to my mind is "Cloud-based ESP32 Morse Code Device", which need to store sensitive messages interpreted from morse code to the database. So we can use this library to perform encryption then store the encrypted messages (ciphertexts) to the database. Here's an example of basic one, which just a constant string of plaintext. The second image is to decipher/decrypt the ciphertext from the ESP32.
-
SHA-256 Hash
- People might confuse hash with encryption. The most basic difference between them is, encryption is two-way, you can encrypt and decrypt the message with corresponding key, while hash is one-way, irreversible, you can hash it but you cannot get the original input/message after you hash it. Most basic usage of hash to verify originality/authenticity of data, because even a bit (0 and 1) is modified, the entire output of hash will be very different, making us easier to check if the data is not original/authentic. In real-world scenario of this library, probably a "sign-up and login web page". We know that ESP32 can act as a web server, thus it is possible to create a web page for signing up and logging in, which need to use hash function to store and verify the passwords while maintaining the secure implementation of passwords storing in database. Here's an example of a constant string being hashed and stored in database.
Installation
Using Arduino Library Manager
Search my library "ESP32_MySQL" or my name "Syafiqlim" on library manager of your Arduino IDE. Install the latest release.
Manual Installation
- Download the latest release from the releases page.
- Open your Arduino IDE.
- Sketch -> Include Library -> Add .ZIP Library...
- Select .zip file you downloaded.
Examples
Here are the basic examples of coding you can use. Feel free to experiment, combine, mix and modify with your logical creativity!
License
This project is licensed under the MIT License. See the LICENSE file for more details.
Acknowledgement
This library is modified and optimized (for ESP32) version of MySQL_MariaDB_Generic by Khoi Hoang and MySQL_Connector_Arduino by Dr. Charles Bell