Disabling ONLY_FULL_GROUP_BY In Ubuntu

I’m run into some error message like:  “Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column”.  It’s turns out that this is caused by MySQL updating the way it behave in relation to aggregate functions since MySQL 5.7.  Here is how I solved it on Ubuntu .

You can find out what mode your MySQL server is running:

SELECT @@GLOBAL.sql_mode;

My Ubuntu 19.10 got this:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Then take this and delete “ONLY_FULL_GROUP_BY”.  You can then add this to MySQL configuration file.    On my Ubuntu there is a file located at /etc/mysql/my.cnf and inside here is specifies two folders where you could put the configuration:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

I have edited /etc/mysql/conf.d/mysql.cnf and put in

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

 

OpenSSH Xauth Command Injection Vulnerability – Ubuntu 14.04 – PCI Compliance

I’m just going through PCI Compliance for a company that I work for. The security scan picked an apparent vulnerability in the open-ssh server. The vulnerability had been patched in Ubuntu 14.04 and so this is a false positive. I thought that I post put about it because I found lots of posts where people are trying to compile their the latest open-ssh servers to get around this problem – when in fact it isn’t actually a problems. Compiling your own version of the open-ssh server isn’t recommended because you will have to continually patch the package yourself from then on.

The warning text said:

Threat:
OpenSSH (OpenBSD Secure Shell) is a set of computer programs providing encrypted communication sessions over a computer network using the SSH protocol.

The sshd server fails to validate user-supplied X11 authentication credentials when establishing an X11 forwarding session. An authenticated user may inject arbitrary xauth commands by sending an x11 channel request that includes a newline character in the x11 cookie.
Please note that Systems with X11Forwarding enabled are affected.

Affected Versions:
OpenSSH versions prior to 7.2p2
Impact:
An authenticated, remote attacker can exploit this vulnerability to execute arbitrary commands on the targeted system.
Solution:
Users are advised to upgrade to the latest version of the software available. Refer to OpenSSH 7.2p2 Release Notes for further information.

Patch:
Following are links for downloading patches to fix the vulnerabilities:

OpenSSH 7.2p2
Result:
SSH-2.0-OpenSSH_6.6.1p1 Ubuntu-2ubuntu2.8 detected on port 22 over TCP.

I hope this is helpful to someone.

How To Install PHP 5.6 on Ubuntu 16.04

I just upgraded one of my Ubuntu Servers to Ubuntu 16.04.  The media wiki installation shopped working because it now comes with PHP 7.  I tried upgrading mediawiki to the latest version but this didn’t seem to work either.   I can’t seem to find out if Mediawiki support PHP 7.  So, I thought I would install PHP 5.6 in parallel on Ubuntu 16.04 by using a PPA (which I generally don’t like)

Here are the commands that I used:

add-apt-repository ppa:ondrej/php
apt update
apt install php5.6 libapache2-mod-php5.6 php5.6-curl php5.6-gd php5.6-mbstring php5.6-mcrypt php5.6-mysql
a2dismod php7.0
a2enmod php5.6
systemctl restart apache2

Hope this helps someone.

 

Apache config error Invalid command ‘php_flag’ Ubuntu 16.04

I have just started to upgrade some servers to Ubuntu 16.04.  It looked good until I rebooted to the server and the Apache2 service didn’t start up properly.  I view the logs in systemd.  Using the command:

systemctl status apache2.service

● apache2.service – LSB: Apache2 web server
Loaded: loaded (/etc/init.d/apache2; bad; vendor preset: enabled)
Drop-In: /lib/systemd/system/apache2.service.d
└─apache2-systemd.conf
Active: failed (Result: exit-code) since Tue 2016-04-26 13:29:37 EDT; 7min ago
Docs: man:systemd-sysv-generator(8)
Process: 1343 ExecStart=/etc/init.d/apache2 start (code=exited, status=1/FAILURE)

Apr 26 13:29:37 hostname apache2[1343]:  * The apache2 configtest failed.
Apr 26 13:29:37 hostname apache2[1343]: Output of config test was:
Apr 26 13:29:37 hostname apache2[1343]: AH00526: Syntax error on line 59 of /etc/apache2/apache2.conf:
Apr 26 13:29:37 hostname apache2[1343]: Invalid command ‘php_flag’, perhaps misspelled or defined by a module not included in the server configuration
Apr 26 13:29:37 hostname apache2[1343]: Action ‘configtest’ failed.
Apr 26 13:29:37 hostname apache2[1343]: The Apache error log may have more information.
Apr 26 13:29:37 hostname systemd[1]: apache2.service: Control process exited, code=exited status=1
Apr 26 13:29:37 hostname systemd[1]: Failed to start LSB: Apache2 web server.
Apr 26 13:29:37 hostname systemd[1]: apache2.service: Unit entered failed state.
Apr 26 13:29:37 hostname systemd[1]: apache2.service: Failed with result ‘exit-code’.

I did a Apache2ctl config test like so:

AH00526: Syntax error on line 59 of /etc/apache2/apache2.conf:
Invalid command ‘php_flag’, perhaps misspelled or defined by a module not included in the server configuration
Action ‘configtest’ failed.
The Apache error log may have more information.

The problem is the setting “php_flag engine off” and “php_admin_value engine Off” settings.  Which looks like so.  This setting stops php files being executed in certain folders (such a image folder etc).

<DirectoryMatch ^.*/somewhere/>
AllowOverride None
  php_flag engine off
php_admin_value engine Off
</DirectoryMatch>

I removed both of these and restarted apache again using:

sudo systemctl start apache2.service

Hope this helps someone else.

How to clear apt-cacher cache – Ubuntu 14.04 – apt-cacher: Cache Full

We have a server running apt-cacher to help cache Ubuntu repositories.  However, after about 2 years of usage the disk on the server has filled up.  I discovered this after trying to update my Ubuntu desktop and got the following error:

503  apt-cacher: Cache Full

Here is how to clear the apt-cache cache.

sudo service apt-cacher stop
sudo rm -rf /var/cache/apt-cacher-ng/
sudo mkdir -p /var/cache/apt-cacher-ng/{headers,import,packages,private,temp}
sudo service apt-cacher start

How give a user permission to run a specfic command as root Ubuntu

Sometimes it can be really useful to give a user privileges to run a specific command as root. For example – if you wanted to allow a user to restart a specific service or run a specific program as root without given them access to the whole server. You can configure this using visudo command as root. Note: you really must be root when you do this and not a sudo.

 sudo su -

Then..

visudo

You can then modify the configuration file. There are specific sections in the config file which are show below. For example – I want to give a user called debug permission to run supervisorctl as root.

....

# User alias specification
debug ALL=(ALL) NOPASSWD:/usr/bin/supervisorctl

Hope this is helpful.

How to monitor bandwidth on Ubuntu Server

I wanted to monitor the bandwidth usage on my Ubuntu server without a full blown graphic monitoring system but something that I could just check once in a while just to see how things are running. I found an really simple tool called vnstat. It’s good because it’s simple, fast and easy to use and that doesn’t require a PhD in networking to get going. It also nicely presents the data with good summaries.

Here is how I setup network traffic monitoring using vmstat:

1 – Install vnstat – note there is another program called vmstat – so be careful what you type.

sudo apt-get install vnstat

2 – Configure the network adapter to monitor. Vnstat automatically configures the network adapters however it’s worth checking that they are correct and having a quick peek through the configuration file.

sudo nano /etc/vnstat.conf

If you do have made any change then don’t forget to restart the service.

sudo service vnstat restart

3 – Get you first report – You will have to wait a few minutes to get your first report. This how a typical report looks like:

#vnstat

outputs:


Database updated: Sun Nov  9 20:11:55 2014

   eth0 since 07/22/14

          rx:  4.56 GiB      tx:  109.97 GiB      total:  114.53 GiB

   monthly
                     rx      |     tx      |    total    |   avg. rate
     ------------------------+-------------+-------------+---------------
       Oct '14      1.24 GiB |   38.39 GiB |   39.63 GiB |  124.13 kbit/s
       Nov '14    361.70 MiB |    9.56 GiB |    9.91 GiB |  108.84 kbit/s
     ------------------------+-------------+-------------+---------------
     estimated      1.20 GiB |   32.43 GiB |   33.62 GiB |

   daily
                     rx      |     tx      |    total    |   avg. rate
     ------------------------+-------------+-------------+---------------
     yesterday     37.62 MiB |  805.67 MiB |  843.29 MiB |   79.96 kbit/s
         today     29.32 MiB |  176.40 MiB |  205.73 MiB |   23.18 kbit/s
     ------------------------+-------------+-------------+---------------
     estimated        34 MiB |     209 MiB |     243 MiB |

Hope this is useful to someone. Let’s me know if you have any improvements to this post or can recommend other tools that readers of this blog might be interested in.

sudo: can’t open /etc/sudoers: permission denied

The sudo command checks the permission of the /etc/sudoers file and also the contents of /etc/sudoers.d/ before allowing you to use the sudo command.

For example you might be warned:

sudo: /etc/sudoers is mode 0777, but should be 0440

or perhaps:

sudo: can't open /etc/sudoers: permission denied.
sudo: no valid sudoers sources foundm quitting

Therefore, if you don’t have root login (which is generally good security practise) then you might not be able to change the permission back without dropping into recovery mode.

1 – Reboot the machine

2 – Select boot from recovery more

3 – fsck the disk which will then mount the disks as read/write

4 – Drop to root shell

5 – chmod 0400 /etc/sudoers

6 – reboot the machine and you are fixed