Category: mariadb

MariaDB – SQL Injection Protection ”; Previous Next The simple act of accepting user input opens the door to exploits. The problem stems primarily from the logical management of data, but luckily, it is fairly easy to avoid these major flaws. Opportunities for SQL injection typically occur on users entering data like a name, and the code logic failing to analyze this input. The Code, instead, allows an attacker to insert a MariaDB statement, which will run on the database. Always consider data entered by users, suspect and are in need of strong validation prior to any processing. Perform this validation through pattern matching. For example, if the expected input is a username, restrict entered characters to alphanumeric chars and underscores, and to a certain length. Review an example given below − if(check_match(“/^w{8,20}$/”, $_GET[”user_name”], $matches)) { $result = mysql_query(“SELECT * FROM system_users WHERE user_name = $matches[0]”); } else { echo “Invalid username”; } Also, utilize the REGEXP operator and LIKE clauses in creating input constraints. Consider all types of necessary explicit control of input such as − Control the escape characters used. Control the specific appropriate data types for input. Limit input to the necessary data type and size. Control the syntax of entered data. Do not allow anything outside of the needed pattern. Control the terms permitted. Blacklist SQL keywords. You may not know the dangers of injection attacks, or may consider them insignificant, but they top the list of security concerns. Furthermore, consider the effect of these two entries − 1=1 -or- * Code allowing either of those to be entered along with the right command may result in revealing all user data on the database or deleting all data on the database, and neither injection is particularly clever. In some cases, attackers do not even spend time examining holes; they perform blind attacks with simple input. Also, consider the pattern matching and regular expression tools provided by any programming/scripting language paired with MariaDB, which provide more control, and sometimes better control. Print Page Previous Next Advertisements ”;

MariaDB – Quick Guide ”; Previous Next MariaDB – Introduction A database application exists separate from the main application and stores data collections. Every database employs one or multiple APIs for the creation, access, management, search, and replication of the data it contains. Databases also use non-relational data sources such as objects or files. However, databases prove the best option for large datasets, which would suffer from slow retrieval and writing with other data sources. Relational database management systems, or RDBMS, store data in various tables.Relationships between these tables are established using primary keys and foreign keys. RDBMS offers the following features − They enable you to implement a data source with tables, columns, and indices. They ensure the integrity of references across rows of multiple tables. They automatically update indices. They interpret SQL queries and operations in manipulating or sourcing data from tables. RDBMS Terminology Before we begin our discussion of MariaDB, let us review a few terms related to databases. Database − A database is a data source consisting of tables holding related data. Table − A table, meaning a spreadsheet, is a matrix containing data. Column − A column, meaning data element, is a structure holding data of one type; for example, shipping dates. Row − A row is a structure grouping related data; for example, data for a customer. It is also known as a tuple, entry, or record. Redundancy − This term refers to storing data twice in order to accelerate the system. Primary Key − This refers to a unique, identifying value. This value cannot appear twice within a table, and there is only one row associated with it. Foreign Key − A foreign key serves as a link between two tables. Compound Key − A compound key, or composite key, is a key that refers to multiple columns. It refers to multiple columns due to a column lacking a unique quality. Index − An index is virtually identical to the index of a book. Referential Integrity − This term refers to ensuring all foreign key values point to existing rows. MariaDB Database MariaDB is a popular fork of MySQL created by MySQL”s original developers. It grew out of concerns related to MySQL”s acquisition by Oracle. It offers support for both small data processing tasks and enterprise needs. It aims to be a drop-in replacement for MySQL requiring only a simple uninstall of MySQL and an install of MariaDB. MariaDB offers the same features of MySQL and much more. Key Features of MariaDB The important features of MariaDB are − All of MariaDB is under GPL, LGPL, or BSD. MariaDB includes a wide selection of storage engines, including high-performance storage engines, for working with other RDBMS data sources. MariaDB uses a standard and popular querying language. MariaDB runs on a number of operating systems and supports a wide variety of programming languages. MariaDB offers support for PHP, one of the most popular web development languages. MariaDB offers Galera cluster technology. MariaDB also offers many operations and commands unavailable in MySQL, and eliminates/replaces features impacting performance negatively. Getting Started Before you begin this tutorial, make sure you have some basic knowledge of PHP and HTML, specifically material discussed in our PHP and HTML tutorials. This guide focuses on use of MariaDB in a PHP environment, so our examples will be most useful for PHP developers. We strongly recommend reviewing our PHP Tutorial if you lack familiarity or need to review. MariaDB – Installation All downloads for MariaDB are located in the Download section of the official MariaDB foundation website. Click the link to the version you would like, and a list of downloads for multiple operating systems, architectures, and installation file types is displayed. Installing on LINUX/UNIX If you have intimate knowledge of Linux/Unix systems, simply download source to build your install. Our recommended way of installing is to utilize distribution packages. MariaDB offers packages for the following Linux/Unix distributions − RedHat/CentOS/Fedora Debian/Ubuntu The following distributions include a MariaDB package in their repositories − openSUSE Arch Linux Mageia Mint Slackware Follow these steps to install in an Ubuntu environment − Step 1 − Login as a root user. Step 2 − Navigate to the directory containing the MariaDB package. Step 3 − Import the GnuPG signing key with the following code − sudo apt-key adv –recv-keys –keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db Step 4 − Add MariaDB to the sources.list file. Open the file, and add the following code − sudo add-apt-repository ”deb http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntuprecise main” Step 5 − Refresh the system with the following − sudo apt-get update Step 6 − Install MariaDB with the following − sudo apt-get install mariadb-server Installing on Windows After locating and downloading an automated install file (MSI), simply double click the file to start the installation. The installation wizard will walk you through every step of installation and any necessary settings. Test the installation by starting it from the command prompt. Navigate to the location of the installation, typically in the directory, and type the following at the prompt − mysqld.exe –console If the installation is successful, you will see messages related to startup. If this does not appear, you may have permission issues. Ensure that your user account can access the application. Graphical clients are available for MariaDB administration in the Windows environment. If you find the command line uncomfortable or cumbersome, be sure to experiment with them. Testing the Installation Perform a few simple tasks to confirm the functioning and installation of MariaDB. Use the Admin Utility to Get Server Status View the server version with the mysqladmin binary. [root@host]# mysqladmin –version It should display the version, distribution, operating system, and architecture. If you do not see the output of that type, examine your installation for issues. Execute Simple Commands with a Client Bring up the command prompt for MariaDB. This should connect you to MariaDB and allow execution of commands. Enter a simple command as follows − mysql> SHOW DATABASES; Post- Installation After successful installation

MariaDB – Backup Methods ”; Previous Next Data serves as the foundation of business and operations, and with various possible threats (e.g., attackers, system failures, bad upgrades, and maintenance errors) out there, backups remain critical. These backups take many forms, and many options exist for creating them with an even wider set of options within those processes. The important things to remember are the database type, the critical information, and the structure involved. This information determines your best option. OPTIONS The main options for backups include logical backups and physical backups. Logical backups hold SQL statements for restoring data. Physical backups contain copies of data. Logical backups offer the flexibility of restoring data on another machine with a different configuration in contrast to physical backups, which are often limited to the same machine and database type. Logical backups occur at database and table level, and physical occur at directory and file level. Physical backups are smaller in size than logical, and also take less time to perform and restore. Physical backups also include log and configuration files, but logical backups do not. Backup Tools The main tool used for MariaDB backups is mysqldump. It offers logical backups and flexibility. It also proves an excellent option for small databases. Mysqldump dumps data into SQL, CSV, XML, and many other formats. Its output does not retain stored procedures, views, and events without explicit instruction. There are three options for mysqldump backups − Raw data − Dump a table as a raw data file through the –tab option, which also specifies the destination of the file − $ mysqldump -u root -p –no-create-info –tab=/tmp PRODUCTS products_tbl Data/Definitions export − This option allows a single or multiple tables to be exported to a file, and supports backing up all existing databases on the host machine. Examine an example of exporting contents or definitions to a file $ mysqldump -u root -p PRODUCTS products_tbl > export_file.txt Transfer − You can also output databases and tables to another host $ mysqldump -u root -p database_name | mysql -h other-host.com database_name Using THE SELECT…INTO OUTFILE Statement Another option for exporting data employs the SELECT…INTO OUTFILE statement. This simple option outputs the table into a simple formatted text file − mysql> SELECT * FROM products_tbl -> INTO OUTFILE ”/tmp/products.txt”; Its attributes allow formatting the file to your preferred specifications. Note the following qualities of this statement − The file name must specify your desired location for the output. You need MariaDB file privileges to execute the statement. The output file name must be unique. You need login credentials on the host. In a UNIX environment, the output file is world readable, but its server ownership affects your ability to delete it. Ensure you have privileges. Using CONNECT in Backups The CONNECT handler allows exporting of data. This proves useful primarily in situations when the SELECT…INTO OUTFILE operation does not support the file format. Review the following example − create table products engine = CONNECT table_type = XML file_name = ”products.htm” header = yes option_list = ”name = TABLE,coltype = HTML,attribute = border = 1;cellpadding = 5” select plugin_name handler, plugin_version version, plugin_author author, plugin_description description, plugin_maturity maturity from information_schema.plugins where plugin_type = ”STORAGE ENGINE”; Other Tools Other options for backups are as follows − XtraBackup − This option targets XtraDB/InnoDB databases and works with any storage engine. Learn more about this tool from Percona”s official site. Snapshots − Some filesystems allow snapshots. The process consists of flushing the tables with read lock, mounting the snapshot, unlocking the tables, copying the snapshot, and then unmounting the snapshot. LVM − This popular method employs a Perl script. It gets a read lock on every table and flushes caches to disk. Then it gets a snapshot and unlocks the tables. Consult the official mylvmbackup website for more information. TokuBackup − This solution provided by Percona provides hot backups taking into account the problems and limitations of InnoDB backup options. It produces a transactional sound copy of files while applications continue to manipulate them.Consult the Percona website for more information.. INNODB Considerations InnoDB uses a buffer pool for performance enhancement. In a backup, configure InnoDB to avoid copying an entire table into the buffer pool because logical backups typically perform full table scans. Print Page Previous Next Advertisements ”;

MariaDB – Useful Resources ”; Previous Next The following resources contain additional information on MariaDB. Please use them to get more in-depth knowledge on this. Useful Links on MariaDB MariaDB − Official Website of MariaDB MariaDB Wiki − Wikipedia Reference for MariaDB Useful Books on MariaDB To enlist your site on this page, please drop an email to [email protected] Print Page Previous Next Advertisements ”;

Discuss MariaDB ”; Previous Next MariaDB is a fork of the MySQL relational database management system. The original developers of MySQL created MariaDB after concerns raised by Oracle”s acquisition of MySQL. This tutorial will provide a quick introduction to MariaDB, and aid you in achieving a high level of comfort with MariaDB programming and administration. Print Page Previous Next Advertisements ”;

MariaDB – Backup Loading Methods ”; Previous Next In this chapter, we will learn about various backup loading methods. Restoring a database from a backup is a simple and sometimes terribly long process. There are three options in loading data: the LOAD DATA statement, mysqlimport, and a simple mysqldump restore. Using LOAD DATA The LOAD DATA statement functions as a bulk loader. Review an example of its use that loads a text file − mysql> LOAD DATA LOCAL INFILE ”products_copy.txt” INTO TABLE empty_tbl; Note the following qualities of a LOAD DATA statement − Use the LOCAL keyword to prevent MariaDB from performing a deep search of the host, and use a very specific path. The statement assumes a format consisting of lines terminated by linefeeds (newlines) and data values separated by tabs. Use the FIELDS clause to explicitly specify formatting of fields on a line. Use the LINES clause to specify line ending. Review an example below. mysql> LOAD DATA LOCAL INFILE ”products_copy.txt” INTO TABLE empty_tbl FIELDS TERMINATED BY ”|” LINES TERMINATED BY ”n”; The statement assumes columns within the datafile use the same order of the table. If you need to set a different order, you can load the file as follows − mysql> LOAD DATA LOCAL INFILE ”products_copy.txt” INTO TABLE empty_tbl (c, b, a); Using MYSQLIMPORT The mysqlimport tool acts as a LOAD DATA wrapper allowing the same operations from the command line. Load data as follows − $ mysqlimport -u root -p –local database_name source_file.txt Specify formatting as follows − $ mysqlimport -u root -p –local –fields-terminated-by=”|” –lines-terminated-by=”n” database_name source_file.txt Use the —columns option to specify column order − $ mysqlimport -u root -p –local –columns=c,b,a database_name source_file.txt Using MYSQLDUMP Restoring with mysqldump requires this simple statement for loading the dump file back into the host − shell> mysql database_name < source_file.sql SPECIAL CHARACTERS AND QUOTES In a LOAD DATA statement, quotes and special characters may not be interpreted correctly. The statement assumes unquoted values and treats backslashes as escape characters. Use the FIELDS clause to specify formatting. Point to quotes with “ENCLOSED BY,” which causes the stripping of quotes from data values. Change escapes with “ESCAPED BY.” Print Page Previous Next Advertisements ”;

MariaDB – Sequences ”; Previous Next In version 10.0.3, MariaDB introduced a storage engine known as sequence. Its ad hoc generates an integer sequence for operations, and then it terminates. The sequence contains positive integers in descending or ascending order, and uses a starting, ending, and increment value. It does not allow use in multiple queries, only in its original query because of its virtual (not written to disk) nature. However, sequence tables can be converted to standard tables through an ALTER command. If a converted table is deleted, the sequence table still exists. Sequences also cannot produce negative numbers or rotate at the minimum/maximum. Installing the Sequence Engine Using sequences requires installing the sequence engine, which MariaDB distributes as a plugin rather than binary. Install it with the following command − INSTALL SONAME “ha_sequence”; After installation, verify it − SHOW ENGINESG Remember that after engine installation, you cannot create a standard table with a name that uses sequence syntax, but you can create a temporary table with a sequence-syntax name. Creating Sequence There are two methods of sequence creation − Create a table and use the AUTO_INCREMENT attribute to define a column as auto-increment. Use an existing database and use a sequence SELECT query to produce a sequence. The query uses seq_ [FROM] _to_[TO] or seq_[FROM]_to_[TO]_step_STEP syntax. Best practices prefer the use of the second method. Review an example of a sequence creation given below − SELECT * FROM seq_77_to_99; Sequences have many uses − Locate missing values within a column to protect against related issues in operations − SELECT myseq.seq FROM seq_22_to_28 myseq LEFT JOIN table1 t ON myseq.seq = x.y WHERE x.y IS NULL; Construct a combination of values − SELECT x1.seq, x2.seq FROM seq_5_to_9 x1 JOIN seq_5_to_9 x2 ORDER BY 5, 6; Find multiples of a number − SELECT seq FROM seq_3_to_100_step_4; Construct a date sequence for use in applications like booking systems. Construct a time sequence. Print Page Previous Next Advertisements ”;

MariaDB – Useful Functions ”; Previous Next This chapter contains a list of the most frequently used functions, offering definitions, explanations, and examples. MariaDB Aggregate Functions Most frequently used aggregate functions are given below − Sr.No Name & Description 1 COUNT It counts the number of records. Example − SELECT COUNT(*) FROM customer_table; 2 MIN It reveals the minimum value of a set of records. Example − SELECT organization, MIN(account) FROM contracts GROUP BY organization; 3 MAX It reveals the maximum value of a set of records. Example − SELECT organization, MAX(account_size) FROM contracts GROUP BY organization; 4 AVG It calculates the average value of a set of records. Example − SELECT AVG(account_size) FROM contracts; 5 SUM It calculates the sum of a set of records. Example − SELECT SUM(account_size) FROM contracts; MariaDB Age Calculation The TIMESTAMPDIFF function provides a way to calculate age − SELECT CURDATE() AS today; SELECT ID, DOB, TIMESTAMPDIFF(YEAR,DOB,”2015-07-01”) AS age FROM officer_info; MariaDB String Concatenation The CONCAT function returns the resulting string after a concatenation operation. You can utilize one or more arguments. Review its syntax given below − SELECT CONCAT(item, item,…); Review the following example − SELECT CONCAT(”Ram”, ”bu”, ”tan”); Output:Rambutan MariaDB Date/Time Functions Given below are important date functions − Sr.No Name & Description 1 CURDATE() It returns the date in yyyy-mm-dd or yyyymmdd format. Example − SELECT CURDATE(); 2 DATE() It returns the date in multiple formats. Example −CREATE TABLE product_release_tbl (x DATE); 3 CURTIME() It returns the time in HH:MM:SS or HHMMSS.uuuuuu format. Example − SELECT CURTIME(); 4 DATE_SUB() It adds or subtracts a number of days from the specified date. Example − SELECT DATE_SUB(”2016-02-08”, INTERVAL 60 DAY); 5 DATEDIFF() It determines the days between two dates. Example − SELECT DATEDIFF(”2016-01-01 23:59:59”,”2016-01-03”); 6 DATE ADD() It adds or subtracts any unit of time to/from the date and time. Example − SELECT DATE_ADD(”2016-01-04 23:59:59”, INTERVAL 22 SECOND); 7 EXTRACT() It extracts a unit from the date. Example − SELECT EXTRACT(YEAR FROM ”2016-01-08”); 8 NOW() It returns the current date and time in either yyyy-mm-dd hh:mm:ss or yyyymmddhhmmss.uuuuuu format. Example − SELECT NOW(); 9 DATE FORMAT() It formats the date in accordance with the specified format string. Example − SELECT DATE_FORMAT(”2016-01-09 20:20:00”, ”%W %M %Y”); Following are some important time functions − Sr.No Name & Description 1 HOUR() It returns the hour of the time, or the hours elapsed. Example − SELECT HOUR(”19:17:09”); 2 LOCALTIME() It functions exactly like NOW(). 3 MICROSECOND() It returns the microseconds of the time. Example − SELECT MICROSECOND(”16:30:00.543876”); 4 MINUTE() It returns the minutes of the time. Example − SELECT MINUTE(”2016-05-22 17:22:01”); 5 SECOND() It returns the seconds of the date. Example − SELECT SECOND(”2016-03-12 16:30:04.000001”); 6 TIME_FORMAT() It formats the time in accordance with the specified format string. Example − SELECT TIME_FORMAT(”22:02:20”, ”%H %k %h %I %l”); 7 TIMESTAMP() It provides a timestamp for an activity in the format yyyy-mm-dd hh:mm:dd. Example − CREATE TABLE orders_ (ID INT, tmst TIMESTAMP); MariaDB Numeric Functions Given below are some important numeric functions in MariaDB − Sr.No Name & Description 1 TRUNCATE() It returns a truncated number to decimal place specification. Example − SELECT TRUNCATE(101.222, 1); 2 COS() It returns the cosine of x radians. Example − SELECT COS(PI()); 3 CEILING() It returns the smallest integer not below x. Example − SELECT CEILING(2.11); 4 DEGREES() It converts radians to degrees. Example − SELECT DEGREES(PI()); 5 DIV() It performs integer division. Example − SELECT 100 DIV 4; 6 EXP() It returns e to the power of x. Example − SELECT EXP(2); 7 FLOOR() It returns the largest integer not above x. Example − SELECT FLOOR(2.01); 8 LN() It returns the natural logarithm of x. Example − SELECT LN(3); 9 LOG() It returns the natural logarithm or the logarithm to a given base. Example − SELECT LOG(3); 10 SQRT() It returns the square root. Example − SELECT SQRT(16); MariaDB String Functions Important string functions are given below − Sr.No Name & Description 1 INSTR() It returns the position of the first instance of a substring. Example − SELECT INSTR(”rambutan”, ”tan”); 2 RIGHT() It returns the rightmost string characters. Example − SELECT RIGHT(”rambutan”, 3); 3 LENGTH() It returns the byte length of a string. Example − SELECT LENGTH(”rambutan”); 4 LOCATE() It returns the position of the first instance of a substring. Example − SELECT LOCATE(”tan”, ”rambutan”); 5 INSERT() It returns a string, with a specified substring at a certain position, that was modified. Example − SELECT INSERT(”ramputan”, 4, 1, ”b”); 6 LEFT() It returns the leftmost characters. Example − SELECT LEFT(”rambutan”, 3); 7 UPPER() It changes characters to uppercase. Example − SELECT UPPER(lastname); 8 LOWER() It changes characters to lowercase. Example − SELECT LOWER(lastname); 9 STRCMP() It compares strings and returns 0 when they are equal. Example − SELECT STRCMP(”egg”, ”cheese”); 10 REPLACE() It returns a string after replacing characters. Example − SELECT REPLACE(”sully”, ”l”, ”n”); 11 REVERSE() It reverses characters in a string. Example − SELECT REVERSE(”racecar”); 12 REPEAT() It returns a string repeating given characters x times. Example − SELECT REPEAT(”ha ”, 10); 13 SUBSTRING() It returns a substring from a string, starting at position x. Example − SELECT SUBSTRING(”rambutan”,3); 14 TRIM() It removes trailing/leading characters from a string. Example − SELECT TRIM(LEADING ”_” FROM ”_rambutan”); Print Page Previous Next Advertisements ”;

MariaDB – Managing Duplicates ”; Previous Next MariaDB, as discussed in earlier lessons, allows duplicate records and tables in some situations. Some of these duplicates are not in fact duplicates due to distinct data or object types, or as a result of unique lifespan or storage of the operation object. These duplicates also typically pose no problems. In some situations, duplicates do cause problems, and they often appear due to implicit actions or the lenient policy of a MariaDB command. There are ways to control this issue, find duplicates, delete duplicates, and prevent duplicate creation. Strategies and Tools There are four key ways to manage duplicates − Fish for them with JOIN, and delete them with a temporary table. Use INSERT…ON DUPLICATE KEY UPDATE to update on discovery of a duplicate. Use DISTINCT to prune the results of a SELECT statement and remove duplicates. Use INSERT IGNORE to stop insertion of duplicates. Using Join with a Temporary Table Simply perform a semi-join like an inner join, and then remove the duplicates found with a temporary table. Using INSERT When INSERT…ON DUPLICATE KEY UPDATE discovers a duplicate unique or primary key, it performs an update. On discovery of multiple unique keys, it updates only the first. Hence, do not use it on tables with multiple unique indices. Review the following example, which reveals what happens in a table containing indexed values on insertion into a populated field − INSERT INTO add_dupl VALUES (1,”Apple”); ERROR 1062 (23000): Duplicate entry ”1” for key ”PRIMARY” Note − If it finds no key, an INSERT…ON DUPLICATE KEY UPDATE statement executes like a normal insert statement. Using DISTINCT DISTINCT clauses remove duplicates from results. The general syntax for a DISTINCT clause is as follows − SELECT DISTINCT fields FROM table [WHERE conditions]; Note − The results of a statement with a DISTINCT clause − When using one expression, it returns unique values for it. When using multiple expressions, it returns unique combinations. It does not ignore NULL values; thus, results also contain NULLs as unique values. Review the following statement using a DISTINCT clause for a single expression − SELECT DISTINCT product_id FROM products WHERE product_name = ”DustBlaster 5000”; Review the following example using multiple expressions − SELECT DISTINCT product_name, product_id FROM products WHERE product_id < 30 Using INSERT IGNORE An INSERT IGNORE statement instructs MariaDB to cancel insertion on discovery of a duplicate record. Review an example of its use given below − mysql> INSERT IGNORE INTO customer_tbl (LN, FN) VALUES( ”Lex”, ”Luther”); Also, note the logic behind duplicates. Some tables require duplicates based on the nature of that table data. Accommodate that need in your strategy for managing duplicate records. Print Page Previous Next Advertisements ”;

MariaDB – Table Cloning ”; Previous Next Some situations require producing an exact copy of an existing table. The CREATE…SELECT statement cannot produce this output because it neglects things like indexes and default values. The procedure for a duplicating a table is as follows − Utilize SHOW CREATE TABLE to produce a CREATE TABLE statement that details the entire structure of the source table. Edit the statement to give the table a new name, and execute it. Use an INSERT INTO…SELECT statement if you also need the table data copied. mysql> INSERT INTO inventory_copy_tbl ( product_id,product_name,product_manufacturer,ship_date) SELECT product_id,product_name,product_manufacturer,ship_date, FROM inventory_tbl; Another method for creating a duplicate uses a CREATE TABLE AS statement. The statement copies all columns, column definitions, and populates the copy with the source table”s data. Review its syntax given below − CREATE TABLE clone_tbl AS SELECT columns FROM original_tbl WHERE conditions]; Review an example of its use below − CREATE TABLE products_copy_tbl AS SELECT * FROM products_tbl; Print Page Previous Next Advertisements ”;