May
13
2019
--

Market map: the 200+ innovative startups transforming affordable housing

In this section of my exploration into innovation in inclusive housing, I am digging into the 200+ companies impacting the key phases of developing and managing housing.

Innovations have reduced costs in the most expensive phases of the housing development and management process. I explore innovations in each of these phases, including construction, land, regulatory, financing, and operational costs.

Reducing Construction Costs

This is one of the top three challenges developers face, exacerbated by rising building material costs and labor shortages.

May
06
2015
--

Percona Security Advisory CVE-2015-1027

Contents

  1. Summary
  2. Analysis
  3. Mitigating factors
  4. P.O.C
  5. Acknowledgments

Summary

During a code audit performed internally at Percona, we discovered a
viable information disclosure attack when coupled with a MITM attack
in which percona-toolkit and xtrabackup perl components could be
coerced into returning additional MySQL configuration information.
The vulnerability has since been closed.

Timeline

2014-12-16 Initial research, proof of concept exploitation and report completion
2015-01-07 CVE reservation request to Mitre, LP 1408375
2015-01-10 CVE-2015-1027 assigned
2015-01-16 Initial fix code completion, testing against POC verified fix
2015-01-23 Internal notification of impending 2.2.13 release of Percona-toolkit
2015-01-26 2.2.13 percona toolkit released: blog post
2015-02-17 2.2.9 xtrabackup released: blog post
2015-05-06 Publication of this document

Analysis

The vulnerability exists in the –version-check functionality of the
perl scripts (LP 1408375), whilst the fix implemented for CVE-2014-2029
(LP 1279502) did patch the arbitrary command execution, MySQL
configuration information may still be exfiltrated by this method.

The normal HTTP/HTTPS conversation is as follows during a –version-check
call.

GET / HTTP/1.1
User-Agent: HTTP-Micro/0.01
Connection: close
Host: v.percona.com

HTTP/1.0 200 OK
Date: Mon, 15 Dec 2014 13:43:12 GMT
Server: Apache
Set-Cookie: PHPSESSID=bjtu6oic82g07rgr9b5906qrg1; path=/
cache-control: no-cache
Content-Length: 144
Vary: Accept-Encoding
Connection: close
Content-Type: text/plain; charset=UTF-8
X-Pad: avoid browser bug

OS;os_version
MySQL;mysql_variable;version_comment,version
Perl;perl_version
DBD::mysql;perl_module_version
Percona::Toolkit;perl_module_version

POST / HTTP/1.1
User-Agent: HTTP-Micro/0.01
Content-Type: application/octet-stream
Connection: close
X-Percona-Toolkit-Tool: pt-online-schema-change
Content-Length: 287
Host: v.percona.com

d6ca3fd0c3a3b462ff2b83436dda495e;DBD::mysql;4.021
1b6f35cca661d68ad4dfceeebfaf502e;MySQL;(Debian) 5.5.40-0+wheezy1
d6ca3fd0c3a3b462ff2b83436dda495e;OS;Debian GNU/Linux Kali Linux 1.0.9
d6ca3fd0c3a3b462ff2b83436dda495e;Percona::Toolkit;2.2.12
d6ca3fd0c3a3b462ff2b83436dda495e;Perl;5.14.2

HTTP/1.0 200 OK
Date: Mon, 15 Dec 2014 13:43:13 GMT
Server: Apache
Set-Cookie: PHPSESSID=nnm4bs99gef0rhepdnclpin233; path=/
cache-control: no-cache
Content-Length: 0
Vary: Accept-Encoding
Connection: close
Content-Type: text/plain; charset=UTF-8
X-Pad: avoid browser bug

The issue centers around the interpretation of the response string

MySQL;mysql_variable;version_comment,version

This could be modified to extract additional information, for example the
ssl_key path.

MySQL;mysql_variable;version_comment,version,ssl_key

The program flow for –version-check is as follows
pingback -> parse_server_response -> get_versions -> sub_for_type.
The issue is the literal lookup of MySQL variables version_comment,
version, ssl_key (version 2.2.13 hard codes these to version_comment,version).

There also exists an issue with silent HTTP “downgrade” when SSL connection fails
in versions < 2.2.13.

7077 my $protocol = 'https'; # optimistic, but...
7078 eval { require IO::Socket::SSL; };
7079 if ( $EVAL_ERROR ) {
7080 PTDEBUG && _d($EVAL_ERROR);
7081 $protocol = 'http';
7082 }

Mitigating factors

This does require an existing presence in order to perform
the MITM attack, and spoof responses from v.percona.com.

This attack is limited to disclosing MySQL configuration information only, no data ex-filtration is known via this method at this time.

Debian && Ubuntu distribution packagers disabled this code in response to CVE-2014-2029

POC

Python stand alone

Github GIST

MSF Module

Gihub GIST

Acknowledgements

  • Frank C – Percona (percona-toolkit dev)
  • Alexey K – Percona (percona-xtrabackup dev)
  • Peter S – Percona (Opensource director)
  • David B – Percona (ISA)
  • Andrea B – oCERT

The post Percona Security Advisory CVE-2015-1027 appeared first on MySQL Performance Blog.

Jul
08
2013
--

Poll: What programming languages and platforms do you use?

What Programming Languages and Platforms do you use?What programming languages and platforms do you use for large-scale projects in your organization?

If something is missing from the list please leave a comment and share your story. Thanks!

Note: There is a poll embedded within this post, please visit the site to participate in this post’s poll.

The post Poll: What programming languages and platforms do you use? appeared first on MySQL Performance Blog.

May
17
2013
--

Percona XtraBackup 2.1.2 for MySQL available for download

Percona XtraBackup for MySQL Percona is glad to announce the release of Percona XtraBackup 2.1.2 for MySQL on May 18, 2013. Downloads are available from our download site here and Percona Software Repositories.

This release fixes number of high-priority bugs since version 2.1 became GA. It’s advised to upgrade your latest 2.1 version to 2.1.2. This release is the latest stable release in the 2.1 series.

Bugs Fixed:

  • Using Perl’s DBD::MySQL package for server communication instead of spawning the MySQL command line client introduced a regression which caused innobackupex –galera-info option to fail. Bug fixed #1180672.
  • The format of xtrabackup_galera_info was missing the ‘:’ separator between the values of wsrep_local_state_uuid and wsrep_last_committed. Bug fixed #1181222.
  • innobackupex automatic version detection did not work correctly for latest Percona Server and MySQL 5.1 releases which could cause innobackupex to fail. Bugs fixed #1181092, #1181099 and #1180905.
  • When backing up a server that is not a replication slave with the innobackupex –slave-info option, innobackupex failed with a fatal error. Replaced the fatal error with a diagnostic message about innobackupex –slave-info being ignored in such a case. Bug fixed #1180662.
  • Low values for wait_timeout on the server could cause server to close the connection while backup is being taken. Fixed by setting the bigger value for wait_timeout option on the server to prevent server from closing connections if the global wait_timeout value is set too low. Bug fixed #1180922.

Other bug fixes: bug fixed #1177182.

Release notes with all the bugfixes for Percona XtraBackup 2.1.2 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

The post Percona XtraBackup 2.1.2 for MySQL available for download appeared first on MySQL Performance Blog.

Apr
08
2013
--

Understanding the maximum number of columns in a MySQL table

Understanding the maximum number of columns in a MySQL tableThis post was initially going to be two sets of polls: “What is the maximum number of columns in MySQL?” and “What is the minimum maximum number of columns in MySQL?”. Before you read on, ponder those questions and come up with your own answers… and see if you’re right or can prove me wrong!

Back in 2009, I finished what seemed an epic task in the Drizzle code base: banishing the FRM file. Why? We felt it was not a good idea to keep arbitrary and obscure limitations from the 1980s alive in the 21st century and instead wanted a modular system where the storage engines themselves owned their own metadata. This was a radical departure from the MySQL philosophy, and one that has truly paid off in the Drizzle code base. However… for those using MySQL, Percona Server, MariaDB or any other of the MySQL branches/forks, you get to have these odd limitations.

Why do I discuss the FRM file? If we look at the MAX_FIELDS define in MySQL, we’ll see that it’s defined as 4096. This, however, is not the true limit. To find out what the actual limit is, we must delve further into the FRM file.

What is an FRM file? It’s the FoRM file from UNIREG. It’s FRM rather than FORM as, well, you used to only get three characters after a dot in a file name. Back in 1979, Monty developed an in-house database named UNIREG. UNIREG was a text-based program for entering records into an ISAM-style database. It would have an 80×24 text UI for entering data and a separate UI for generating reports. This evolved into the SQL based MySQL, with MySQL 1.0 being released in 1995.

The FoRM file specified what fields where on what screen for entering, as well as any constraints as to what could be entered. For example, if you had more than a certain number of fields, you were going to need more than one 80×24 screen to enter in all the data! You could also have things like NEXT_NUMBER fields (what we know today as auto_increment), CASEUP and CASEDN fields which although not implemented in MySQL, the defines can still found in the source. Basically, it’s why we can’t have nice things (like default values other than NOW()).

It also has certain limits which by any modern standard are purely arbitrary. One of those is the limit that a certain part of the FRM file cannot be greater than 64kb. The bit of code in question that comes into play around the maximum number of columns is this:

/* Hack to avoid bugs with small static rows in MySQL */
reclength=max(file->min_record_length(table_options),reclength);
if (info_length+(ulong) create_fields.elements*FCOMP+288+
    n_length+int_length+com_length > 65535L || int_count > 255)
{
  my_message(ER_TOO_MANY_FIELDS, ER(ER_TOO_MANY_FIELDS), MYF(0));
  DBUG_RETURN(1);
}

Which is, of course, obvious! Various parts of this limit are:

  • info_length is roughly 2+strlen(field_name) for each field. Unless you have many columns, and then it’s something else (as in that case you don’t get your 80×24 terminal UI in your FRM file, you instead get some bunch of bytes per 19 columns).
  • create_fields.elements*FCOMP is just number of fields multiplied by 17
  • 288 is static and is always needed
  • int_length is the interval length. This isn’t the normal meaning of the word interval, we can only guess that it’s called this due to either something UNIREG specific or it’s just Monty’s English language skills in the 1980s. We’ll come back to this one.
  • com_length is the length of all the comments for each field (but not the table)

An interval in UNIREG speak is a set of strings that are the options for ENUM or SET columns. The tricky bit is that it’s unique intervals, not actual intervals, so two ENUM columns both having the options ‘Y’ and ‘N’ will use less space in the FRM than if you had one with ‘Y’ and ‘N’ and the other with ‘A’ and ‘B’.

If you noticed that if you have a long comment on each field you reduce the number of different ENUM elements you can have, you are correct. There is also a limit of 255 unique intervals, so while you can have many more ENUM(‘Y’,’N’) columns, you can only have 255 ENUM columns with unique values.

If you were looking for a very basic formula that is mostly accurate, I present this:

foreach field:  17+2*(strlen(field_name)+2) (bytes)
    + length of all comments (in bytes)
    + length of all intervals (for ENUM, SET) in bytes.

If you use that as a rule of thumb, with that not being able to exceed 64k, you’re roughly on the right track to working out the maximum number of columns in a MySQL table.

So what’s the maximum number of columns in a MySQL table? Well.. I tried a few things before I settled on the following (perl) program (accepts command line parameter of number of columns to create) to produce the CREATE TABLE sql statement:

sub cname ($) {
  my $c=shift;
  my $name="";
  while($c > 0)
  {
    my $n=$c%36;
    $name.=chr(ord('0')+$n) if $n < 10;     $name.=chr(ord('a')+($n-10)) if $n >= 10;
    $c= int $c/36;
  }
  return $name
}
my $sql= "CREATE TABLE t (";
 foreach(1..shift @ARGV) {
    my $n=cname($_);
    $sql.="`$n`";
    $sql.=" ENUM('Y','N','M','0','1','2')\n";
 }
 chop $sql;
 chop $sql;
 $sql.=");";
 print $sql;

This gets you a 46kb CREATE TABLE statement and a 76kb FRM file for a table with 2,829 columns. I believe this to be the maximum number of columns you can create.

If you try setting the storage engine to InnoDB however, you will get an error message! The exact error message you get is not very interesting and just says “Can’t create table ‘test.t’ (errno: 139)”. This is because InnoDB has a hard limit of 1,000 columns. This is the code from ha_innodb.cc that enforces that limit:

if (form->s->fields > 1000) {
  /* The limit probably should be REC_MAX_N_FIELDS - 3 = 1020,
  but we play safe here */
  DBUG_RETURN(HA_ERR_TO_BIG_ROW);
}

Which is curiously gone from MySQL 5.6, it’s replaced by the following set of defines

#define	DATA_N_SYS_COLS 3 /* data0type.h */
/* from rem0types.h */
#define REC_MAX_N_FIELDS        (1024 - 1)
#define REC_MAX_N_USER_FIELDS	(REC_MAX_N_FIELDS - DATA_N_SYS_COLS * 2)

Which adds up to (1024-1)-3*2=1017 which is exactly the same as what I can create. That’s right folks, in MySQL 5.6 you can create a table with a few more columns in InnoDB!

This led me on another idea… what is the minimum maximum number of columns you can create? You may think that it is 255 based on the limit of the number of intervals above, but can you get any lower? Why yes you can! With this bit of perl code I was able to hit a too many columns error with only 192 columns (i.e. 191 worked):

sub cname ($$) {
     my $c=shift;
     my $name="";
     while($c > 0)
     {
	 my $n=$c%36;
	 $name.=chr(ord('0')+$n) if $n < 10; 	 $name.=chr(ord('a')+($n-10)) if $n >= 10;
	 $c= int $c/36;
     }
     $name.='0' foreach(length $name .. shift);
     return $name
 }
 my $sql= "CREATE TABLE `".cname(16,63)."` (";
 foreach(1..shift @ARGV) {
     my $n=cname($_,63);
     $sql.="`$n`";
     $sql.=" ENUM('".cname(0,64)."') COMMENT '".cname($_,254)."',\n";
 }
 chop $sql;
 chop $sql;
 $sql.=");";
 print $sql;

So the maximum number of columns for a table in MySQL is somewhere between 191 and 2829, depending on a number of factors. I’d be interested to hear if you’ve been able to beat my minimum/maximums!

The post Understanding the maximum number of columns in a MySQL table appeared first on MySQL Performance Blog.

Feb
22
2013
--

CentOS 5.8 users: your UTF-8 data is in peril with Perl MySQL

CentOS 5.8 and earlier use Perl module DBD::mysql v3.0007 which has a bug that causes Perl not to flag UTF-8 data as being UTF-8.  Presuming that the MySQL table/column is using UTF-8, and the Perl MySQL connection is also using UTF-8, then a correct system returns:

PV = 0x9573840 "\343\203\213 \303\250"\0 [UTF8 "\x{30cb} \x{e8}"]

That’s a Devel::Peek inside a Perl scalar variable which clearly shows that Perl has recognized and flagged the data at UTF-8.  With DBD::mysql v3.0007 however, an incorrect system returns:

PV = 0x92df9a8 "\343\203\213 \303\250"\0

Notice that it’s the same byte sequence (in octal), but there’s no UTF-8 flag.  As far as Perl is concerned, this is Latin1 data.

What does this mean for you?  In general, it means that Perl could corrupt the data by treating UTF-8 data as Latin1 data.  If the program doesn’t alter the data, then the problem is “overlooked” and compensated for by the fact that MySQL knows that the data is UTF-8 and treats it as such.  We have found, however, that a program can modify the data without corrupting it, but this is risky and really only works by luck, so you shouldn’t rely on it.

I’d like to clarifying two things.  First, DBD::mysql v3.0007 was released in September 2006, but this very old problem still exists today because CentOS 5 is still a popular Linux distro.  So this isn’t “breaking news”, and Perl and DBD::mysql have handled UTF-8 correctly for nearly the last decade.   Second, just a reminder: all Percona Toolkit tools that connect to MySQL have a –charset option and an “A” DSN option for setting the character set.

In conclusion, if you

  1. Run CentOS 5
  2. Have UTF-8 data in MySQL
  3. Use Perl to access that data
  4. Have not upgraded DBD::mysql (perl-DBD-MySQL)

then your UTF-8 data is in peril with Perl.  The solution is simple: upgrade to any newer version of DBD::mysql (except 4.014).

The post CentOS 5.8 users: your UTF-8 data is in peril with Perl MySQL appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com