Jaime Frutos Morales's blog


How to install and configure GreenSQL in Ubuntu 9.10

Filed under: Databases, Security, SysAdmin — acidborg @ 13:12

Description: “GreenSQL is an Open Source database firewall used to protect databases from SQL injection attacks. GreenSQL works as a proxy for SQL commands and has built in support for MySQL & PostgreSQL . The logic is based on evaluation of SQL commands using a risk scoring matrix as well as blocking known db administrative commands (DROP, CREATE, etc). GreenSQL is distributed under the GPL license”.


  • Download its source code from its web.
  • install the needed packages: apt-get install libevent-1.4-2 libpcre3 libmysqlclient15off libpq5 libmysqlclient15-dev libevent-dev libpcre3-dev libpcre3 libpq-dev flex g++ bison build-essential
  • Uncompress it: tar xvfz greensql-fw_*.tar.gz
  • Enter its directory: cd greensql-fw_*
  • Build the deb package: ./build.sh
  • Install the deb package (as root): cd .. && dpkg -i greensql-fw*.deb
  • Answer the questions to connect GreenSQL to your database

Configuration (using Apache):

  • Enter GreenSQL directory: cd /usr/share/greensql-fw
  • Set the right permissions to templates_c : chgrp -R www-data templates_c && chmod -R 770 templates_c
  • Create the file /etc/apache2/conf.d/greensql with the following content(replace [ and ] for angle brackets):
    Alias /greensql /usr/share/greensql-fw
    [Directory /greensql]
    Order deny,allow
    Deny from all
    Allow from
  • Restart Apache: apache2ctl restart
  • Access GreenSQL using your web browser (default user is admin and default password is pwd): http://localhost/greensql
  • Change the default admin’s password.
  • Edit GreenSQL configuration to fit your needs (reading this might help).

To use GreenSQL, you have to change the configuration of the applications which connect to your database and point them to the computer where you have installed GreenSQL (localhost in this case) and the port where GreenSQL is running (3305 in my case to proxy my MySQL database). You can test whether it is working connecting to your database and creating a table (it should appear as an alert named “Detected attempt to create database/table/index” in GreenSQL and it should be blocked if you didn’t change the IPS option). Example:
mysql -u root -h -P 3305 -p
CREATE TABLE greensql_test;

Remember: Although you use database firewalls like GreenSQL, you must prevent SQL injection and other database-related attacks by securing and auditing your application’s code.


Create and restore MySQL backups using mysqldump

Filed under: Databases, SysAdmin — acidborg @ 13:02

Description: “The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

To create a backup of a single database: mysqldump --opt -u database_admin_username -p database_name > database_name_backup.sql

To create a backup of all databases: mysqldump --opt -u database_admin_username -p --all-databases > all_databases_backup.sql

To restore a backup: mysql database_name -u database_admin_username -p < database_backup.sql

You might need to create the database you are restoring if you deleted it before:

mysql -u database_admin_username -p
CREATE DATABASE database_name;


How to install and configure Sphinx in Ubuntu 9.10 with MySQL support

Filed under: Databases, Ubuntu — acidborg @ 21:40

Description: “Sphinx is a full-text search engine, distributed under GPL version 2. Commercial license is also available for embedded use. Generally, it’s a standalone search engine, meant to provide fast, size-efficient and relevant fulltext search functions to other applications. Sphinx was specially designed to integrate well with SQL databases and scripting languages. Currently built-in data sources support fetching data either via direct connection to MySQL or PostgreSQL, or using XML pipe mechanism (a pipe to indexer in special XML-based format which Sphinx recognizes). As for the name, Sphinx is an acronym which is officially decoded as SQL Phrase Index. Yes, I know about CMU’s Sphinx project“.


  • Install the packages needed: apt-get install gcc make libmysqlclient15-dev libmysql++-dev
  • Download Sphinx from here.
  • Decompress it: tar xvfz sphinx-*.tar.gz
  • Enter its directory: cd sphinx-*
  • Run configure: ./configure --prefix=/usr/local/sphinx --with-mysql
  • Compile Sphinx: make
  • Install Sphinx (as root): make install


  • Enter Sphinx’s directory: cd /usr/local/sphinx/etc
  • Make a copy of its default configuration: cp sphinx.conf.dist sphinx.conf
  • Modify it to fit you database schema and preferences (I recommend reading Sphinx’s official documentation and this tutorial by IBM first).
  • Create all indexes: /usr/local/sphinx/bin/indexer --all
  • Something like this will be shown:

    Sphinx 0.9.9-rc2 (r1785)
    Copyright (c) 2001-2009, Andrew Aksyonoff

    using config file '/usr/local/sphinx/etc/sphinx.conf'...
    indexing index 'software'...
    collected 10 docs, 0.0 MB
    sorted 0.0 Mhits, 100.0% done
    total 10 docs, 649 bytes
    total 0.013 sec, 48970 bytes/sec, 754.54 docs/sec
    total 2 reads, 0.000 sec, 16.3 kb/call avg, 0.0 msec/call avg
    total 5 writes, 0.000 sec, 0.4 kb/call avg, 0.0 msec/call avg


  • You can perform a basic search using: /usr/local/sphinx/bin/search desired_word . It matches all words by default, but you can change this behaviour. Run /usr/local/sphinx/bin/search without arguments to show the available options.
  • Example: /usr/local/sphinx/bin/search samba

    Sphinx 0.9.9-rc2 (r1785)
    Copyright (c) 2001-2009, Andrew Aksyonoff

    using config file '/usr/local/sphinx/etc/sphinx.conf'...
    index 'software': query 'samba ': returned 1 matches of 1 total in 0.000 sec

    displaying matches:
    1. document=10, weight=2
    description=Samba is a SMB/CIFS file, print, and login server. It seamlessly integrates Linux/Unix Servers and Desktops into Active Directory environments using the Winbind daemon.

    1. 'samba': 1 documents, 2 hits

The use of /usr/local/sphinx/bin/search is just for test and debugging purposes. There are native API ports for PHP, Python, Java, Perl, and Ruby. I will explain the integration between PHP and Sphinx on another post.

Blog at WordPress.com.