Restricting Access to All Pages Using Zend 2 and ZfcUser

A new application that I’m working on needs to block all access to every page, except the login page, for unauthenticated users. After setting up Zend 2 with the ZfcUser module, this is a piece of cake. Just update your module/Application/Module.php file with the following code:


getApplication()->getEventManager();
$moduleRouteListener = new ModuleRouteListener();
$moduleRouteListener->attach($eventManager);

$e->getApplication()->getEventManager()->getSharedManager()->attach('ZendMvcControllerAbstractActionController', 'dispatch', function($e) {

$controller = $e->getTarget();
$sm = $e->getApplication()->getServiceManager();
$auth = $sm->get('zfcuser_auth_service');

if (!$auth->hasIdentity()) {

// If on login / registration page, let them pass
if(
!($e->getRouteMatch()->getParam('controller', 'index') == 'zfcuser' && $e->getRouteMatch()->getParam('action', 'index') == 'login')
&& !($e->getRouteMatch()->getParam('controller', 'index') == 'zfcuser' && $e->getRouteMatch()->getParam('action', 'index') == 'register'))
$controller->plugin('redirect')->toRoute('zfcuser/login');
}
}, 100);

}
}

Wala. All requests, that are not to the login or register pages, are now forwarded to http://yoursite/user/login if a user is not authenticated.

Creating Entities from Existing Database with Doctrine 2 and Zend 2

So I’m upgrading an existing system to use Doctrine, Zend 2 and Dojo and the first step was to setup Doctrine with the existing database. I really didn’t want to have to manually code up all of the Entities so I was looking to see how I could use Doctrines tools to automate this. Here’s how:

(note, I’m using Windows)
1) Create a “tmp” directory in the root of your site

2) From the root of your Zend 2 site, run

php vendordoctrinedoctrine-modulebindoctrine-module orm:convert-mapping —–from-database xml .tmp

3) Once the .dcm.xml files have been generated, you need to tell Doctrine to use the XML driver by updating the vendordoctrinedoctrine-modulebindoctrine-module.php file:


// Add the following three statements above the last two lines
$driverImpl = new DoctrineORMMappingDriverXmlDriver(
array('path/to/your/xml/files'));
/* @var $em DoctrineORMEntityManager */
$em = $application->getServiceManager()->get('doctrine.entitymanager.orm_default');
$em->getConfiguration()->setMetadataDriverImpl($driverImpl);
// end of new code

/* @var $cli SymfonyComponentConsoleApplication */
$cli = $application->getServiceManager()->get('doctrine.cli');
$cli->run();

4) Create the php entity files:

php vendordoctrinedoctrine-modulebindoctrine-modules orm:generate-entities ----generate-annotations="true"

Running Windows PowerShell as Any User

So I have a need to login to other PCs through Windows PowerShell from time to time. Just like I need to SSH to other Linux machines. Well that’s fine, I just open up PS and run


Enter-PSSession computer-name

The problem is that I’m usually logged into my PC with an account that does not have permissions to the other PC. If I run PS as a privileged user though, this isn’t a problem. (Yes, I’m sure there’s a way to pass credentials through the Enter-PSSession cmdlet, but that’s not how I solved this.)

One option was to use runas and start PS from the command line


C:WindowsSystem32runas.exe /user:domainusername C:WindowsSystem32WindowsPowerShellv1.0powershell.exe

but then you don’t get the pretty blue PS window and you’re stuck in the command line.

What I ended up having to do was write a PS script to start a PS session under a certain user:


Start-Process powershell.exe -Credential "" -WorkingDirectory "C:"

I called this ps_admin.ps1 and stuck it on my desktop. So now whenever I want to run a PS session as any user, I just click it and get the following popup

From there, I login with any user I want and get the pretty blue PS screen πŸ™‚

 

Searching All Tables in a SQL Server Database for a value.

One of my IT guys was trying to write a SQL query to search for a keyword in every table and column of a particular database. There are tons of solutions out there on how to do this and from looking at what he had done so far, I decided to see how quickly I could figure out one. Ended up being about 15 minutes.

CAUTION: Probably not the fastest or most optimized solution, but fairly simple:


USE My_Database;

-- Store results in a local temp table so that. I'm using a
-- local temp table so that I can access it in SP_EXECUTESQL.
CREATE TABLE #tmp (
tbl nvarchar(max),
col nvarchar(max),
val nvarchar(max)
);

DECLARE @tbl nvarchar(max);
DECLARE @col nvarchar(max);
DECLARE @q nvarchar(max);
DECLARE @search nvarchar(max) = 'my search key';

-- Create a cursor on all columns in the database
declare c cursor for
SELECT tbls.TABLE_NAME, cols.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLES AS tbls
JOIN INFORMATION_SCHEMA.COLUMNS AS cols
ON tbls.TABLE_NAME = cols.TABLE_NAME

-- For each table and column pair, see if the search value exists.
OPEN c
FETCH NEXT FROM c INTO @tbl, @col
WHILE @@FETCH_STATUS = 0
BEGIN
-- Look for the search key in current table column and if found add it to the results.
SET @q = 'INSERT INTO #tmp SELECT ''' + @tbl + ''', ''' + @col + ''', ' + @col + ' FROM ' + @tbl + ' WHERE ' + @col + ' LIKE ''%' + @search + '%'''
EXEC SP_EXECUTESQL @q
FETCH NEXT FROM C INTO @tbl, @col
END
CLOSE c
DEALLOCATE c

-- Get results
SELECT tbl, col, val FROM #tmp

-- Remove local temp table.
DROP TABLE #tmp

Attaching Taxonomy Terms to Existing Drupal Nodes with SQL

A website I work with has a whole bunch of Taxonomy “keywords” that needed to be assigned to existing content. There’s no way I could use third party modules to assign these terms to the existing content because I’m using large data sets (~500,000). Plus, why have the overhead when I just use SQL to directly access manipulate the data.

Below is a SQL statement that will add a taxonomy association to any content that contains the taxonomy keyword:


INSERT INTO term_node
(nid, vid, tid)
(
SELECT q.nid, q.nid, t.tid
FROM content_type_yourtype q
JOIN term_data t
-- Make sure you only find full words, not partial matches
ON q.field_type_value LIKE CONCAT('% ', t.name, ' %')
-- Don't try to add existing entries
WHERE NOT EXISTS (SELECT n.nid FROM term_node n WHERE n.nid = q.nid AND n.vid = q.nid AND n.tid = t.tid)
-- Clear out blanks
AND t.name != ''
-- This is optional, I need it because my hosting provider times out on 3+ min queries...
AND q.nid >= 200000 AND q.nid < 300000 )

SCRATCH THIS. You need to setup a job to run this, it's too much work on the database:

PHP Script to use:


= $min AND q.nid < $max ");

Bash Script to automate:


min=202500;
limit=2000;
for i in {1..150};
do echo "Running Job $i with $j";
php -e tax_update.php $min `expr $min + $limit`;
min=`expr $min + $limit`;
done

Manually Fixing / Adding Permissions to node_access Table (Drupal 6)

For some reason one of the Drupal sites that I work on for one of my clients wasn’t adding new information to the node_access table for new content being created. We noticed this because some of the results weren’t showing up in searches. (They won’t if the node doesn’t have grant_view checked.) Anyhow, I created the following query to fix this:


/*
* This statement is just used to check how many need to be
* sync'd and can be ignored. It does let you know how many
* nodes your missing though πŸ˜‰
*/
SELECT count(*) FROM mydatabase.node n where n.nid not in (
select a.nid from mydatabase.node_access a)
and (n.type = 'content_type_a' or n.type = 'content_type_b');

/*
* Create a temp table to insert all missing permissions.
* Note, this isn't required if you only have one access type,
* but since I three, it is necessary. This tables structure
* is just a replica of node_access table.
*/
create temporary table s (
nid int not null,
gid int,
realm varchar(255),
grant_view tinyint(3),
grant_update tinyint(3),
grant_delete tinyint(3)
);

/*
* I have three node permission groups that I have to change,
* so I will run three insert statements to populate my temp
* table. There is probably a way to combine this into one
* SQL query, but that's another days lesson.
*/
insert into s
/* This group gets view access */
select n.nid, GID_VAL, 'term_access', 1, 0, 0
from mydatabase.node n
where n.nid not in (
select a.nid from mydatabase.node_access a
)
and (n.type = 'content_type_a' or n.type = 'content_type_b');

insert into s
/* This group gets view access */
select n.nid, GID_VAL, 'term_access', 1, 0, 0
...

insert into s
/* This group gets view, update and delete access */
select n.nid, GID_VAL, 'term_access', 1, 1, 1
...

/*
* Finally add all of the missing node permissions into
* the real node_access table!
*/
insert into mydatabase.node_access
(nid, gid, realm, grant_view, grant_update, grant_delete)
select s.nid, s.gid, s.realm, s.grant_view, s.grant_update, s.grant_delete
from s;

/*
* You might wonder why you need the temp table, it's cause the
* where clause would return no rows after that first INSERT.
* If you only have one GID to worry about, you can forget
* the temp table step.
*/

/*
* Confirm you get 0 results. (No missing nodes)
*/
SELECT count(*) FROM mydatabase.node n where n.nid not in (
select a.nid from mydatabase.node_access a)
and (n.type = 'content_type_a' or n.type = 'content_type_b');

/*
* Remove temp.
*/
drop table s;

Yes, you could use just rebuild all of the permissions through Drupal, but when the site has an extremely large node population, this takes hours.

Adding Column Messes Up Existing SQL View

So I needed to add a new form field to an existing application, which usually requires a new column to a SQL table. Cool, no biggie. Everything is fine and dandy until I go to SharePoint to view the external lists connected to the database, I kept getting errors about string to datetime conversion. WTF…

These external lists were reading from SQL Views, so I took a look at the content coming from the views, and about halfway through the columns everything was shifted right by one. This happened at exactly where I inserted the new column (I didn’t append it to the table). The issue is that when I created the view, I used a select * instead of implicitly stating the columns. When you do this, I guess SQL Server expands * to all existing columns to make “select a, b, c, d from table” and saves THAT as the View query. So when the “select” from view was being called from SharePoint, it was referencing the old “c” column position but now that was different. (NULL for all existing rows)

This should help you to visualize:

Before:
1) Created view with select *
2) SQL Server expanded to select a, b, c, d and saved as view.
3) Now by selecting all from view, it’s using: select a, b, c, d from myView

a | b | c | d
—————
1 | 2 | 3 | 4

After:
1) Added column newCol after column b
2) View now still calling using: select a,b, c, d from myView, but now newCol was being used for c, and then c was used:

a | b | c | d
——————
1 | 2 | NULL | 3

AH d is using c column value!! So the rest are now off by one.

To fix, just ran ALTER on the view

But lesson learned, DON’T create views with SELECT *

Bottom of Two dijit.InlineEditBox with dijit.form.Textarea Editor Does Not Open on Single Click

dijit.InlineEditBox is great, but if you have two stacked on each other in containers with undefined heights, things can get a little buggy for a user. Example:

The problem is that if you open up id=”one” first, the page height expands two rows to make a textarea, so when you click on id=”two”, the page re renders and doesn’t open “two”! (This will work going from “two” to “one” since the top part of the page won’t change.)

If you watch the focus and blur events you’d see the following from “two” to “one”:

Focus: two
Blur: two
… click one
… two closes
Focus: one
… one opens
Blur: one

If you watch the focus and blur events you’d see the following from “one” to “two”:

Focus: one
Blur: one
… click two
… one closes
Focus: two
…. NOTHING …..

Basically for some reason it doesn’t open. So to fix this, just implicitly call edit() during onFocus.


...
onFocus: function()
{
this.inherited(arguments);
this.edit(); // Force (technically it may get called twice now, oh well.)
},
....

dijit.InlineEditBox editWidget is undefined

So I have a save all button to save all dijit.form elements at once, but first I wanted to make sure that they were all valid. Piece of cake, all of them have isValid() methods so all I have to do is loop through them all and check to see if they are all valid… wrong.

Apparently you can not access the editor, editWidget, editNode etc of an dijit.InlineEditBox until AFTER it has already been displayed. This is retarded since it is HIGHLY likely that a user won’t open every edit box on the page *sigh*. This took some digging in the source code to find out, but if you look at the edit() function for dijit.InlineEditBox around line 190:


if(this.wrapperWidget){
this.wrapperWidget.editWidget.attr("displayedValue" in this.editorParams ? "displayedValue" : "value", this.value);
}else{
// Placeholder for edit widget
// Put place holder (and eventually editWidget) before the display node so that it's positioned correctly
// when Calendar dropdown appears, which happens automatically on focus.
var placeholder = dojo.create("span", null, this.domNode, "before");

// Create the editor wrapper (the thing that holds the editor widget and the save/cancel buttons)
var ewc = dojo.getObject(this.editorWrapper);
this.wrapperWidget = new ewc({
value: this.value,
buttonSave: this.buttonSave,

You see that the editor isn’t created until the actual edit function is called. So basically to force all editors to be created:


try{
inlineeditbox.edit();
// THIS IS UNDEFINED until edit();
inlineeditbox.wrapperWidget.editWidget.isValid();
}
catch(e){
console.log('Editor doesn't have validation');
}