PHP, Perl, MySQL web programming tutorials, howtos for beginners and novice users

Posted on in Categories Apache, FAQ, Howto, Linux, MySQL, UNIX last updated June 6, 2007

The motto of this site is learn with pictures, not words:

In Pictures tutorials began as part of a research study we conducted for the U.S. Department of Education. The goal: to make it easier for people with learning disabilities to learn computer subjects.

As part of the study, we created simple, illustration-based tutorials. Everyone who tested them–not just people with learning disabilities–said the new tutorials enabled them to learn faster and easier than conventional text-heavy books.

The site offers tutorials on common Linux web programming subject:
=> PHP

=> Perl

=> MySQL

Great work! (Thanks to [email protected] for a hat tip)

Howto: Connect MySQL server using C program API under Linux or UNIX

Posted on in Categories C Programming, FreeBSD, Gentoo Linux, Howto, Linux, MySQL, OpenBSD, RedHat/Fedora Linux, Solaris, Suse Linux, Tips, Ubuntu Linux, UNIX last updated May 31, 2007

From my mailbag:

How do I write a C program to connect MySQL database server?

MySQL database does support C program API just like PHP or perl.

The C API code is distributed with MySQL. It is included in the mysqlclient library and allows C programs to access a database.

Many of the clients in the MySQL source distribution are written in C. If you are looking for examples that demonstrate how to use the C API, take a look at these clients. You can find these in the clients directory in the MySQL source distribution.

Requirements

Make sure you have development environment installed such as gcc, mysql development package etc. Following is the list summarize the list of packages to compile program:

  • mysql: MySQL client programs and shared library
  • mysqlclient: Backlevel MySQL shared libraries (old libs)
  • mysql-devel: Files for development of MySQL applications (a must have)
  • mysql-server: Mysql server itself
  • gcc, make and other development libs: GNU C compiler

Sample C Program

Following instructions should work on any Linux distro or UNIX computer. Here is the small program that connects to mysql server and list tables from mysql database.(download link):

/* Simple C program that connects to MySQL Database server*/
#include <mysql.h>
#include <stdio.h>

main() {
   MYSQL *conn;
   MYSQL_RES *res;
   MYSQL_ROW row;

   char *server = "localhost";
   char *user = "root";
   char *password = "PASSWORD"; /* set me first */
   char *database = "mysql";

   conn = mysql_init(NULL);

   /* Connect to database */
   if (!mysql_real_connect(conn, server,
         user, password, database, 0, NULL, 0)) {
      fprintf(stderr, "%s\n", mysql_error(conn));
      exit(1);
   }

   /* send SQL query */
   if (mysql_query(conn, "show tables")) {
      fprintf(stderr, "%s\n", mysql_error(conn));
      exit(1);
   }

   res = mysql_use_result(conn);

   /* output table name */
   printf("MySQL Tables in mysql database:\n");
   while ((row = mysql_fetch_row(res)) != NULL)
      printf("%s \n", row[0]);

   /* close connection */
   mysql_free_result(res);
   mysql_close(conn);
}

How do I compile and link program against MySQL libs?

MySQL comes with a special script called mysql_config. It provides you with useful information for compiling your MySQL client and connecting it to MySQL database server. You need to use following two options.
Pass –libs option – Libraries and options required to link with the MySQL client library.

$ mysql_config --libs
Output:

-L/usr/lib64/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib64 -lssl -lcrypto

Pass –cflags option – Compiler flags to find include files and critical compiler flags and defines used when compiling the libmysqlclient library.
$ mysql_config --cflags
Output:

-I/usr/include/mysql -g -pipe -m64 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing

You need to pass above option to GNU C compiler i.e. gcc. So to compile above program, enter:
$ gcc -o output-file $(mysql_config --cflags) mysql-c-api.c $(mysql_config --libs)
Now execute program:
$ ./output-file
Output:

MySQL Tables in mysql database:
columns_priv
db
func
help_category
help_keyword
help_relation
help_topic
host
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user 

References:

  • MySQL C API – A must read – official MySQL C API documentation

Download Google MySQL patches

Posted on in Categories Download of the day, MySQL last updated April 25, 2007

Google released new programming code under the GPL license to enhance MySQL databases.

These are patches for MySQL 4.0.26. They add many features that enhance the manageability and reliability of MySQL.

The patch works with MySQL version 4 and Google expects version 5 support shortly.

The patches include a few big features and many enhancements. The big features are:

* SemiSyncReplication – block commit on a master until at least one slave acknowledges receipt of all replication events.
* MirroredBinlogs – maintain a copy of the master’s binlog on a slave
* TransactionalReplication – make InnoDB and slave replication state consistent during crash recovery
* UserTableMonitoring – monitor and report database activity per account and table
* InnodbAsyncIo – support multiple background IO threads for InnoDB
* FastMasterPromotion – promote a slave to a master without restart

google-mysql-tools (Via informationweek)

Rumor: Oracle to offer Unbreakable MySQL

Posted on in Categories GNU/Open source, MySQL, News last updated January 29, 2007

Computer Business Weblogreporting interesting news – Oracle has suggested it will offer support for the MySQL code, undercutting the company,

According to Mickos, MySQL’s chief executive officer , the database giant is planning to repeat its October 2006 Unbreakable Linux plan, which saw it undercut Red Hat with enterprise Linux support.

To be frank, I don’t know what Oracle is doing here. They already have Oracle database and now started to mess with MySQL.

Sure, competition is good for all of us. At the end of day we will see better service, IMPO.

Will Oracle launch Unbreakable MySQL?

MySQL avoid unauthorized reading and SQL Injection vulnerabilities in PHP

Posted on in Categories Howto, Linux, MySQL, Security, Tips, UNIX last updated January 6, 2007

The LOAD DATA statement can load a file that is located on the server host, or it can load a file that is located on the client host when the LOCAL keyword is specified.

In a Web server environment where the clients are connecting from a Web server, a user could use LOAD DATA LOCAL to read any files that the Web server process has read access to (assuming that a user could run any command against the SQL server). In this environment, the client with respect to the MySQL server actually is the Web server, not the remote program being run by the user who connects to the Web server. Attacker can take advantage of this SQL injection via PHP/perl etc.

Open my.cnf file:
# vi my.cnf
Append following line [mysqld] section
local-infile=0

Save and restart MySQL server:
# /etc/init.d/mysql restart

MySQL create an Anonymous or limited access only account

Posted on in Categories Linux, MySQL, Security, Sys admin, Tips, UNIX last updated January 5, 2007

Sometime it is necessary to create an anonymous or limited access only account. This allows anonymous user to use MySQL server. I received following criteria

[*] Create a user called anonymous

[*] Set up read and write permission to account

[*] anonymous cannot set or update password (remember if one user changes the password, no other anonymous login can be accepted again).

[*] Grant anonymous access to table called xyz

Please note that this anonymous user is for internal WAN/Lan user and not for Internet users.

However, I have noticed default anonymous mysql account.
$ mysql -u anonymous

MySQL allows to login in anonymous user (or any user) from localhost. However this user is not allowed to use any / critical database such as mysql or set password:
$ mysql -u anonymous
Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2630835 to server version: 4.1.20
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Try to use mysql database:
mysql> use mysql;
Output:

ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'

Try to setup password:
mysql> SET PASSWORD FOR [email protected]=PASSWORD('secrete');
Output:

ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'

So all I have to do is set permission to table called xyz with GRANT SQL command.
$ mysql -u root -p
Now grant permission to xyz table:
mysql> use dbnane;
mysql> GRANT SELECT ON xyz TO [email protected]

Now any anonymous user can connect to mysql database server and query xyz table from localhost.

Comparison PostgreSQL vs MySQL database server

Posted on in Categories Links, MySQL last updated December 19, 2006

Both are open source databases. This is old debate. Yes no one can expect one database fits all your jobs.

To choose between the two databases, you need to understand whether you need the transaction support of Postgres or the large-text-area support in MySQL.

From the article:
It’s interesting that the two databases appear to be converging to meet in the middle somewhere. While MySQL is working on adding transaction support and slowly adding features like subselects, Postgres is making progress in the performance and stability.

It compare two databases using following points:

  • SQL standard COMPLIANCE
  • PLATFORMS
  • Speed
  • STABILITY
  • DATA INTEGRITY
  • SPECIAL server-side FEATURES
  • Security
  • LOCKING and CONCURRENCY SUPPORT
  • LARGE OBJECTS etc

A good read (little old too) but don’t forget to make some test running before selecting your database.

Howto Setup Apache 2, MySQL 5, and PHP 5 (SAMP) for SUN Solaris 10 UNIX

Posted on in Categories Apache, Howto, MySQL, php, Solaris, UNIX last updated November 29, 2006

Many documents and blogs can be found on the Internet explaining how to use the Solaris 10 OS for a SAMP server (Solaris, Apache 2, MySQL, PHP). However, many of these articles are for older versions of the software packages or do not include the popular PHP language. Some of them lack any detail or examples to help you understand the process.

This article shows the technical user how to build their own up-to-date copy of PHP5 and integrate it with MySQL5 from Blastwave and the version of Apache2 included with the Solaris OS.

For this exercise, the installation will use the following software:

* Apache as delivered with the Solaris installation
* MySQL 5, from Blastwave.org, using pkg-get to install it
* The latest PHP 5 from php.net, downloaded and compiled

SAMP (Solaris, Apache 2, MySQL 5, and PHP 5) Setup for Solaris 10 OS and Solaris Express

Save time use MySQL/MariaDB auto completion for database or table names with auto-rehash option

Posted on in Categories Howto, MySQL last updated May 17, 2017

There is a quick way to type both MySQL database and table names quickly by enabling MySQL auto completion feature. This is called automatic rehashing. This option is on by default, which allows table and column name completion with MySQL command line:
Continue reading “Save time use MySQL/MariaDB auto completion for database or table names with auto-rehash option”