Projects Posts

Realeases and Works in progress.

Yii2-parsel v1.0.0 Released

Turns a user query like 'georgia -(atlanta or decatur)' into 'georgia AND NOT (atlanta or decatur)
Posted by a scalded mole rat on Thursday, July 26th, 2018 11:59 AM
yii2, yii2 extension, boolean query, sphinx, user query, activequery

Use it to search every Seinfeld episode!

Yii2 Parsel

Allows developers to provide a boolean search query interface, similar to Google or Sphinx search or other full-text search (FTS) engines.

Turns a user query like 'georgia -(atlanta or decatur)' into 'georgia AND NOT (atlanta or decatur)' which is then turned into the follow SQL:

SELECT 
 "ip", /* ip address */
 "visits", /* how many requests they've made */
 "city",
 "region"
FROM
/* A table similar to apaches access log. See my extension yii2-ipFilter */
 "visitor"
WHERE
(
  ("visitor"."ip" ILIKE '%georgia%')
   OR ("visitor"."city" ILIKE '%georgia%')
   OR ("visitor"."region" ILIKE '%georgia%')
)
 AND ( /** marvel as we efortlessly generate a subquery */
   "ip" NOT IN (
     SELECT
       "ip"
     FROM
       "visitor"
     WHERE
      (
        ("visitor"."ip" ILIKE '%atlanta%')
         OR ("visitor"."city" ILIKE '%atlanta%')
         OR ("visitor"."region" ILIKE '%atlanta%')
      )
       OR (
        ("visitor"."ip" ILIKE '%decatur%')
         OR ("visitor"."city" ILIKE '%decatur%')
         OR ("visitor"."region" ILIKE '%decatur%')
      )
  )
)

Example results:

IpVisitsCityRegion
107.77.232.21616Georgia
107.77.235.1993Georgia
174.218.142.271LawrencevilleGeorgia
107.77.233.22518Georgia
205.201.132.1442WoodstockGeorgia
192.3.160.154DouglasGeorgia

Installation

The preferred way to install this extension is through composer.

Either run

php composer.phar require --prefer-dist johnsnook/yii2-parsel "*"

or add

"johnsnook/yii2-parsel": "*"

to the require section of your composer.json file.

Usage

"Look, I didn't know I could speak Parseltongue! What else don't I know about myself? Look. Maybe you can do something, even something horrible and not know you did it."

Once the extension is installed, simply use it in your code by :

$userQuery = 'good AND plenty -licorice';
$parsel = new ParselQuery([
           'userQuery' => $this->userQuery,
           'dbQuery' => Script::find()
      ]);
$parsel->dbQuery->all();

Tokens/behavior:

Fields to be search must be either text, varchar or char currently. Future versions may expand to number, dates and maybe even JSON. All search terms, except where specified bye the full match operator are wrapped in your databases wildcard of choice. Searching for "smart" is equivalent to the SQL expression '%smart%'. Search is case insensitive as long as your database's LIKE operator is. PostgreSQL will use ILIKE.

Conjunctives:

'AND' is the default behavior. "smart pretty" is the same as "smart AND pretty."

'OR' allows more results in your query: "smart OR pretty."

Operators:

OperatorTypeDescription
-NegationThe user query "smart pretty -judgmental" parses to "smart AND pretty AND NOT judgmental"
()Sub-queryAllows grouping of terms . The user query "-crazy (smart AND pretty)" parses to "NOT crazy AND (smart AND pretty)"
*WildcardFuzzy matches. "butt*" matches butt, buttery, buttered etc.
_Character wildcardMatches one character. "boo_" matches boot, book, bool, boon, etc.
=Full matchEntire fields must be equal to the term. "=georgia" only matches where one or more fields is exactly equal to the search term. The search term will NOT be bracketed with %, but wildcards can still be used.
""Double quotesPhrase. '"Super fun"' searches for the full phrase, space include. Wild cards, negation and exact match operators all work within the phrase.
''Single quotesPhrase, no wildcards. The term will not be evaluated for * or _, but will be wrapped in wildcards. If a % or _ is in the term, it will be escaped. 'P%on*' becomes '%P\%on*%'.
:FieldSpecify the field to search. 'name:jo*' will search the name field for 'jo*.' If no field name matches, all fields will be searched for 'name:jo*'

Examples

See files in /examples. If it's still up, you might also be able to play with an example here

Additional Reading

PostgreSQL

Faster PostgreSQL Searches with Trigrams

Optimizing databases for fuzzy searching

MySQL

Performance analysis of MySQL's FULLTEXT indexes and LIKE queries for full text search

Acknowledgements

This project was built by heavily modifying the excellent "Search Query Parser" project. I de-abstracted the token structure and modified the parser class to better fit my needs. Their licsence file should be found at the root of this project.

Both projects are made possible by the amazing and lightning quick lexer library by Nikita Popov of Berlin. It's work reading his article on the subject.


How to use a Bootstrap 4 theme with Yii2

In which I share the fruits of my research with you.
Posted by a scalded mole rat on Thursday, July 12th, 2018 12:13 PM
"php, yii2, bootstrap 4"

There seems to be a little confusion on how to do this, and there's no Yii2 cookbook entry that covers all the topics involved, so I hope this will be of some help to some poor schmuck like me.

Install and configure the Bootstrap4 extension

The nice boys over at Yiisoft have already published the Yii2-Bootstrap4 extension, so change "yiisoft/yii2-bootstrap": "~2.0.0" to "yiisoft/yii2-bootstrap4": "~1.0.0" and change "minimum-stability": "stable" to "minimum-stability": "dev" in your composer.json and update it. You can go ahead and delete all that codeception shit, because you don't do unit testing; you're a real man. While composer is taking forever, go ahead and open your assets/AppAsset.phpfile and change

public $depends = [
   'yii\web\YiiAsset',
   'yii\bootstrap\BootstrapAsset',
];

to

public $depends = [
   'yii\web\YiiAsset',
   'yii\bootstrap4\BootstrapAsset',
];

If you end up using the default view files, you might want to do a global search and replace on the views folder and replace "use yii\bootstrap" to "use yii\bootstrap4".

Download and configure your Bootstrap 4 template

It doesn't matter if you're using a basic or advanced Yii2 installation, create a directory in the root of your yii2 project called "themes." Now go download the Bootstrap 4 theme of your choice, create another directory in your new themes directory named whatever your theme is, eg sbclean and unzip it here. For this example, we're going to use Start Bootstrap's "Clean Blog."

For this part, you can pretty much follow this tutorial for theming Yii2, but I'll hit the highlights right quick. I started out by adding to the app components section of my @frontend/config/main.php or @app/config/web.php the config recommended in the tutorial.

'components' => [
  ...
   'view' => [
       'theme' => [
           'basePath' => '@app/themes/sbclean',
           'baseUrl' => '@web/themes/sbclean',
           'pathMap' => [
               '@app/views' => '@app/themes/sbclean',
          ],
      ],
  ],
  ...
],

That setup is for having your view folders in your theme folder, and since I was committed and it was a pain in the ass to keep navigating to the themes folder to tweak layouts or views, I just backed up the @frontend/views/layouts, the @frontend/views/site and the @frontend/views/post directories and changed the view component section to:

'theme' => [
   'basePath' => '@app/themes/sbclean',
   'baseUrl' => '@web',
],

Create an AssetBundle for your theme

Your layouts are going to need the assets (CSS & JS) from your theme, so let's figure out which ones we're going to need. So create a new PHP class in your assets directory called SbcleanAsset.php and set the sourcePath to point at our theme directory thusly: public $sourcePath = __DIR__ . '/../themes/sbclean'; Most templates include an Html example in index.html, so if we open it up and take a look, we see the CSS, located in the <head> tag looking something like:

<head>
...
   <!-- Bootstrap core CSS -->
   <link href="vendor/bootstrap/css/bootstrap.min.css" rel="stylesheet">
<!-- Custom fonts for this template -->
<link href="vendor/font-awesome/css/font-awesome.min.css" rel="stylesheet" type="text/css">
<link href='https://fonts.googleapis.com/css?family=Lora:400,700,400italic,700italic' rel='stylesheet' type='text/css'>
     <link href='https://fonts.googleapis.com/css?family=Open+Sans:300italic,400italic,600italic,700italic,800italic,400,300,600,700,800' rel='stylesheet' type='text/css'>
     <!-- Custom styles for this template -->
<link href="css/clean-blog.min.css" rel="stylesheet">
...
</head>

We don't the need the vendor/bootstrap/css/bootstrap.min.csssince the Bootstrap4 asset already takes care of it, so we'll just add the stuff we need to our AssetBundle like so:

public $css = [
   'vendor/font-awesome/css/font-awesome.min.css',
   '//fonts.googleapis.com/css?family=Lora:400,700,400italic,700italic',
   '//fonts.googleapis.com/css?family=Open+Sans:300italic,400italic,600italic,700italic,800italic,400,300,600,700,800',
   'css/clean-blog.min.css'
];

Now lets scroll to the bottom of our index.html to grab the JS. It might look something like this:

<!-- Bootstrap core JavaScript -->
<script src="vendor/jquery/jquery.min.js"></script>
<script src="vendor/bootstrap/js/bootstrap.bundle.min.js"></script>

<!-- Custom scripts for this template -->
<script src="js/clean-blog.min.js"></script>

Once again, our AppAsset already has the JqueryAsset and BootstrapAsset covered, so we only need to add the template specific line, so it should end up looking like this in your themes' AssetBundle:

public $js = [
'js/clean-blog.min.js'
];

Finally, we need to make sure this assets files are included after our AppAsset so we'll add that to the depends section, so that our final assets/SbcleanAsset.php class looks like:

namespace app\assets;

use yii\web\AssetBundle;

class SbcleanAsset extends AssetBundle {
   public $sourcePath = __DIR__ . '/../themes/sbclean';
   public $css = [
  'vendor/font-awesome/css/font-awesome.min.css',
  '//fonts.googleapis.com/css?family=Lora:400,700,400italic,700italic',
  '//fonts.googleapis.com/css?family=Open+Sans:300italic,400italic,600italic,700italic,800italic,400,300,600,700,800',
   'css/clean-blog.min.css'
  ];
   public $js = [
  'js/clean-blog.min.js'
  ];
   public $depends = [
       'app\assets\AppAsset',
  ];
}

Theming the layouts

Keep that index.html file open, but also open up your views/layouts/main.php file. Let's start with changing this

use app\assets\AppAsset;

AppAsset::register($this);

to

use app\assets\SbcleanAsset;
use yii\helpers\Url;

SbcleanAsset::register($this);

Now we want to match the navbar to our themes style, so remove the `<div class="wrap"></div> , and modify the NavBar options to look like:

    'id' => "mainNav",
   'class' => 'navbar navbar-expand-lg navbar-light fixed-top',

This will use the themes styles on our default Yii2 Nav. Next, copy the <header> block from the index.html and paste it under the Nav::Widget block in your main.php . Then add the themes published base url before the background images URL so that it looks like this:

<!-- Page Header -->
   <header class="masthead" style="background-image: url('<?= $asset->baseUrl ?>/img/home-bg.jpg')">
     <div class="overlay"></div>
     <div class="container">
       <div class="row">
         <div class="col-lg-8 col-md-10 mx-auto">
           <div class="site-heading">
             <h1>Clean Blog</h1>
             <span class="subheading">A Blog Theme by Start Bootstrap</span>
           </div>
         </div>
       </div>
     </div>
   </header>

That's it. Now when you go to your site it should look something like this:



yii2-ipFilter

An extension for Yii2 which provides an access log and filtering.
Posted by a scalded mole rat on Saturday, June 23rd, 2018 11:34 PM
"yii2, blacklist, ip address, access log, php, extension, proxy"

Following the hole-patching I was engaged in to block certain IP addresses (*cough*) and then Tor proxies (*cough**cough*bitch*cough*), I was left with an ad-hoc mess, which worked, but was all over the place.

So after some refactoring and normalization, I turned my sloppy mess into a tight little extension that can be added to any Yii2 project.  Once configured and the migrations performed, it will immediately begin capturing all visits, and will identify and blacklist tor, socks & vpn proxies, depending on the user configuration.  Of course, it also gets the "ip info," including host, lat lon, and AS name.

You can see it in action here, and the repository is here.  It's currently v0.0.2, so I've got a bit more work to do to get it ready for full release.

Future versions will have some GIS visualization, a rule creator so you can just go ahead and block Russians automatically, statistical graphs.  I'd also like to add a controller access rule behavior, so you could, for example, make certain records viewable everywhere except a certain city or state.


Making a controller action for images

If you've got access control on your site, but have images in a public folder, they're not protected.
Posted by a scalded mole rat on Tuesday, June 12th, 2018 6:33 PM
"yii2, controller, php, access control, images"

I have some stalkers that I've blacklisted by ip address, but I checked the logs and saw the primary black hat was still able to access files in a publicly accessable /web/images directory.  Since I have a few articles with those images embedded, I wanted the old path to still work for authorized guests and users. Here's how I did it:

1.) Move the images folder from @frontend/web/ to @frontend/assets

2.) Add an "images" action to the SiteController:

public function actionImages($file) {
        $response = Yii::$app->getResponse();
        $response->headers->set('Content-Type', 'image/jpeg');
        $response->format = Response::FORMAT_RAW;
        $fullPath = __dir__ . "/../assets/images/$file";
        if (!is_resource($response->stream = fopen($fullPath, 'r'))) {
            throw new \yii\web\ServerErrorHttpException('file access failed: permission denied');
        }
        return $response->send();
    }

3.) Add a rule to the url manager in my main config.php

    'images/<file>' => 'site/images',

And that's it.  Now any requests for images go through my controller and I can redirect any blacklisted jerks to the /site/fuckoff page!



My new security system

Due to unfortunate circumstances, I was forced to implement a way to blacklist ip addresses.
Posted by a scalded mole rat on Saturday, June 9th, 2018 10:08 AM
"yii2, blacklist, ip address, access log, php, postgres, cidr"

I thought I had it licked, but wasn't selecting the cidr correctly for single ips, only for subnets.  With that bug fixed, I now have a pretty great little access log that allows me to view the ip, what it requested, and where it's physically located (thanks to the easy API of ipinfo.io!).  It also allows me to add an ip address or range of ips to the blacklist.

The blacklist record itself allows me to address it to the owner of that ip, and give them a custom message.

All my controllers inherit from BaseController, so all I needed to do was add was
public function beforeAction($action) {
    if (parent::beforeAction($action)) {
        Access::log();
        if (Blacklist::isBanned() && $action->id != 'fuckoff') {
            $this->redirect(['site/fuckoff']);
        }
        return true;
    }
    return false;
}
and all the other controllers automatically implement it.  But the real magic happens in the Blacklist::isBanned() method.  This deceptively simple sql query checks to see if the client ip address is equal to or is contained by the blacklists record definition.

SELECT count(*) from blacklist WHERE ip_address <<= ':ip';

So, if a blacklist definition exists for 38.104.188.0/24 and a client with an ip of 38.104.188.130 tries to access the site, they get redirected to the fuckoff page.  Simple.  And all thanks is due to Postgresql "contains or is equal to" operator, "<<=".

Anyway, here's some screenshots.

Here's the access log, with the ip, the city, how many times they accessed the site and the most recent time they requested something.  The black items are blacklisted ips, which have links  to view their blacklist record.  The city comes from the ip_info table, where it's stored in a JSON field.  The select statement looks like:
SELECT DISTINCT 
a.ip_address, count(*) as count, 
max(created_at AT TIME ZONE 'EST') as recent, 
info->>'city' as city 
FROM access a join ip_info i using(ip_address) 
GROUP BY a.ip_address, i.info->>'city'; 


Here's my ex-wife's access details.  As you can see, the last entry is her getting blocked and redirected to the "fuckoff" page, which includes a full screen video of an eye surgery.


There's also a "quick view" that shows the last 20 requests and their age.


This pulls the info from ipinfo.io and stores the json in a json field.


If I decide an ip is coming from an unwanted visitor, I can click the ban button and this will pop up with some defaults.


Once the blacklist record is created, their entry in the grid will be blacked out and this is what you see when you click it's link.


And of course, you can view the blacklist records on their own index page.

So that's my new security system.  So far, it's served me well, though I continue to make little tweaks here and there as data comes in.  Thanks for taking a look, and you can always check it out here, but have a little screen shot anyway.