Iptables’ recent module parameter

I had the pleasure to set up iptables’ recent module in order (hopefully) to stop basic small scale DoS attack on my web server. The module is pretty straightforward to use (as opposed to the limit module with its averages and burst) with two basic settings: seconds and hitcount. By default hitcount cannot be greater than 20 and you will need to change the parameter ‘ip_pkt_list_tot’ if you need more than 20.

Since most likely the recent module is already loaded and changing the parameter will involve either rebooting or unloading the module (and its dependency modules) which is not ideal if it is a live server. Luckily there is a way to change the parameter on the fly. Say you want to increase the hitcount to 100, you can do:

# echo 100 > /sys/module/ipt_recent/parameters/ip_pkt_list_tot

That is it. Just make sure you have the write permission!

September 16, 2011 at 6:52 am Leave a comment

Need Help with SQL

At work, we have a huge user table with all the email addresses. We also have a column acting as a counter that we increment when certain things happen. Recently we found that the counter got incremented on 99% of the email addresses all at once and that really screwed things up. By digging through MySQL logs, I finally found the offending SQL statement (with fake database.table and column name):

UPDATE database.table SET counter = counter + 1 WHERE email = 'In>Ng;>.|-vcduee  YSZTM||I!8e1CIVY?oUO<<ACNanZTMabp*TyaIiaE u\"{XSXveZ|'a=$hB|Fa1Tu?ZObP@cbk3n`0RI..C89-WoTc0Eciie5NSPI2q3...pp(..c]0I*|o27r,,p9Q.....ZTobHIETyYP'-'!#0SaaFAO'

That is one crazy WHERE condition clause. Can anyone see why it matches 99% of the email addresses I have in the table? There is not going to be any prize for figuring it out for me unfortunately but I appreciate any feedback on this! Thanks.

August 23, 2007 at 11:36 pm 1 comment

Turn on VIM Syntax Highlighting

I have noticed that a few people have come to my blog hoping to find out why their VIM doesn’t have syntax highlighting at all. Well, if the syntax highlighting is disabled, you just go in the command mode and do:

:syntax on

Of course, you wouldn’t want to do that every time you edit a file. To make it default, just create/edit the file ~/.vimrc and put “syntax on ” on a line of itself.

July 26, 2007 at 6:55 am Leave a comment

Bugzilla 2.18.3 Stopped Working

At work we use Mozilla’s Bugzilla as our bug tracking solution. It is a great tool coming from the open-source community, by the way. We were still stuck on version 2.18.3 since we saw no need to upgrade it. However, today my co-worker told me he ran into an error while submitting a new bug. I quickly tried to see what the problem was and as soon as I did a search on open bugs, I saw:

Software error:

DBD::mysql::st execute failed: Unknown column 'bugs.bug_id' in 'on clause' [for Statement "SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles AS map_assigned_to  LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  WHERE bugs.assigned_to = map_assigned_to.userid AND (bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED')) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id"] at Bugzilla/DB.pm line 62
	Bugzilla::DB::SendSQL('SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bu...') called at [webroot location]/buglist.cgi line 766

That is odd. First thing I checked was to see if it was a MySQL permission issue. Nope, the user permission settings were just fine. If I ran that SQL statement manually as the Bugzilla user at MySQL console, I got:

ERROR 1054 (42S22): Unknown column 'bugs.bug_id' in 'on clause'

Ok, that showed me something was not right with MySQL, rather than Bugzilla itself (or the Perl DBI and DBD::mysql modules). I did a quick Google search on that “ERROR 1054” and it turned out that it was a bug in MySQL5 with the way it interprets a SQL statement when JOIN is involved. To confirm that, I changed the SQL statement from:

SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles AS map_assigned_to  LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  WHERE bugs.assigned_to = map_assigned_to.userid AND (bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED')) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id

To (notice the parentheses surrounding “bugs, profiles AS map_assigned_to”):

SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM (bugs, profiles AS map_assigned_to)  LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  WHERE bugs.assigned_to = map_assigned_to.userid AND (bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED')) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id

That modified SQL statement worked fine. This also means that to make our version of Bugzilla work with MySQL 5, those statements will need to be changed to work around the bug. That is not an acceptable solution for us to do. I’m sure the Bugzilla team was aware of the problem and should have a solution for it in the latest version.

I went ahead and upgraded to latest version of Bugzilla which is 3.0. Viola! It worked. Bugzilla now works happily with MySQL 5 and my collegeue can finally go back to his bug-crushing activity.🙂

July 16, 2007 at 9:39 pm 2 comments

VIM Syntax Highlighting

For anyone who uses CakePHP 1.2 branch should know the template/view files now end with the .ctp extension (instead of .thtml). However, Vim doesn’t recognize that extension so syntax highlighting won’t be activated when you edit the files in Vim. Last thing I want to do is to code in Vim without syntax highlighting! To remedy the problem, I simply modified the filetype file to tell Vim to syntax highlight .ctp files as .php files. To do so in Ubuntu 7.04, open the filetype:

vi /usr/share/vim/vimcurrent/filetype.vim

Search for the string “php” and you will find the lines you need to modify and just add *.ctp like below:

" Php, php3, php4, etc.
au BufNewFile,BufRead *.ctp,*.php,*.phpd               setf php

Next time you open any .ctp file you will be greeted with those beautiful colors again that inspire us to write more wonderful code in PHP.🙂

July 11, 2007 at 6:26 am 5 comments

Enable MySQLi Extension in PHP

In my previous blog, I talked about MySQL’s stored procedures and how MySQLi support must be enabled in PHP in order to utilize stored procedures. That also means a re-compilation of PHP is necessary.

To compile PHP with MySQLi support, you need to add this to PHP configure:

--with-mysqli[=location of mysql_client]

I always use the binary installs so “mysql_client” is in /usr/local/mysql/bin/. So in my case, the option looks like:

--with-mysqli=/usr/local/mysql/bin/mysql_config

Do a search on that file if you are not sure where it is on your system. Also, make sure the one you found has the same version as the the version of MySQL installed. You can see the version by running:

mysql_client --version

If you are lucky, that is all you need to do to enable MySQLi support. However, if the configure stops with the message, you have a problem:

configure: error: wrong mysql library version or lib not found. Check config.log for more information

Don’t worry. There are two options here. First option is to upgrade MySQL to the latest version which is 5.0.41 (at the time of this writing). 5.0.41 includes the necessary shared library files for PHP to compile for MySQLi support. I am not sure about at what point did MySQL decide to include the files (I know for sure they weren’t in at least versions before 5.0.27). They are probably going to include the files from this point onward.

Second option is to compile the shared libraries yourself. If that is what you decided to do, please read on.

Next, go on to MySQL’s website and download the source corresponding to the version you have on your system. You may need to look into the Archive section if yours isn’t the most up-to-date.

Once you have grabbed the source tar file, do:

tar zxvf mysql-5.x.xx.tar.gz
cd mysql-5.x.xx
./configure --enable-shared
make

You do not need make install since we will keep the binaries we have installed. All what we are doing is to make libmysqlclient.so (shared object) available for PHP to link to.

After make, do:

cd libmysql/.libs
cp libmysqlclient.so.15.0.0 /usr/local/mysql/lib
ln -s /usr/local/mysql/lib/libmysqlclient.so.15.0.0 /usr/local/mysql/lib/libmysqlclient.so
ln -s /usr/local/mysql/lib/libmysqlclient.so.15.0.0 /usr/local/mysql/lib/libmysqlclient.so.15

That is it! Now compile PHP again and you should see MySQLi (mysqli) in phpinfo() output. Remember to restart Apache as well (stop AND start, not graceful restart).

June 22, 2007 at 7:09 pm 4 comments

Learning MySQL 5 Stored Procedures

I recently have been trying to pick up Stored Procedure in MySQL 5. It has been pretty straight forward to learn if you already have done any programming before. Just a matter of familiarizing yourself with the syntax.

If you are learning Stored Procedure from scratch just like me, here are a few gotchas I’ve found:

  • DECLARE statements *must* come at the top of each procedure.
  • Each procedure is tied to a database. Therefore, you must specify the database you want to create procedures for by executing USE database_name before you start working. The same goes with calling the procedures: you either execute USE database_name or call the procedure by prefixing it with the database name (ie, CALL database.procedure()).
  • To those who use replication, yes, stored procedures get replicated to the slaves as well.
  • Avoid using variable names that are actually SQL keywords such as DATE. I haven’t read any documentation that explicitly says not to use the keywords, but I did run into errors when I tried to use one.
  • MySQLi extension is required to call stored procedures from PHP. This is a big consideration you need to account for if you work on shared hosts that do not have MySQLi (which in turns need PHP 5 and MySQL 5). You will get this error “PROCEDURE XXX can’t return a result set in the given context” if you attempt to execute a stored procedure with the good ol’ “mysql” extension.

Of course, I have some tips for you as well:

  • You can use any text editor to write the procedures, but MySQL’s own Query Browser actually works pretty well as an editor for stored procedures. Once a procedure is crated, you can see it by expanding the database tree on the right. Try it.
  • To dynamically generate SQL statements in procedures, you will need to concatenate different parts of the statement together and send it in as an Prepared Statement.

I hope this blog can save headaches for some of you. By the way, I ran into problems while re-compiling PHP for MySQLi support. I will provide my solution soon so stay tuned.

June 19, 2007 at 8:04 pm 1 comment

Older Posts


August 2016
M T W T F S S
« Sep    
1234567
891011121314
15161718192021
22232425262728
293031  

Recent Posts

Top Clicks

  • None

Feeds


Follow

Get every new post delivered to your Inbox.