Stuff I'm Up To

Technical Ramblings

Linux and MS SQL ODBC Authentication — May 16, 2019

Linux and MS SQL ODBC Authentication

My recent troubles spawned from connecting my Laravel application to a Microsoft SQL Server database. My username and password are correct but the connection fails.

In the storage/logs/laravel.log file I found the following:

[2019-05-16 08:22:46] work.ERROR: SQLSTATE[HY000]: [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_1000) (SQL: select * from holidays where holiday_date > '2019-05-16 08:22:46') {"exception":"[object] (Illuminate\Database\QueryException(code: HY000): SQLSTATE[HY000]: [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_1000) (SQL: select * from holidays where holiday_date > '2019-05-16 08:22:46') at /PATH/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDOException(code: HY000): SQLSTATE[HY000]: [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_1000) at /PATH/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:47, PDOException(code: HY000): SQLSTATE[HY000]: [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_1000) at /PATH/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:43)

I haven’t setup any Kerberos on my Linux side to authenticate and it would appear that my MS SQL wants me to use that. So instead of setting up Kerberos I opted for getting the ODBC connection NOT to use Kerberos.

To do this for my specific user account on my development system I created an ~/.odbc.ini file and added details to disable Kerberos:

.odbc.ini

[my_database]
 Driver = ODBC Driver 17 for SQL Server
 Server = myserver.domain.local,1433
 Database = my_database
# If NOT using Kerberos authentication:
 Trusted_Connection = No
 ServerSPN = MSSQLSvc/myserver.domain.local:my_database
# If using SSL encryption:
 Encryption = Yes
# If using SSL and not importing the server certificate into your certificate store:
 TrustServerCertificate = Yes

To ensure that Laravel makes use of this config the only way I could do it was to NOT use the eloquent models and had to use PHP PDO directly.

// We need to use a direct PDO here because of the
// Trusted Connection=No / SSPI required in .odbc.ini
$pdo = new \PDO('sqlsrv:Server='.env('DB_HOST'),
  env('DB_USER'), env('DB_PASSWORD'));

// Create the number of token parameters
$params = implode(',', array_fill(0, count($tokens), '?'));
$sql = "select data from my_table where something in ($params);";
$stmt = $pdo->prepare($sql);
$stmt->execute($tokens);

$rows = $stmt->fetchAll(\PDO::FETCH_ASSOC); // Associated Array only

Advertisements
Laravel Echo and Redis — March 18, 2019

Laravel Echo and Redis

The corporate app just got made a bit smarter.

In order to alert users that something has happened that they should be aware of we’ve added real time notifications. We could have polled a table for notifications, but a far smarter development is to use real time communications with socket.io.

Laravel already has these capabilities, but needs to leverage a couple of other services outside of your web server. Notably, Laravel Echo and a message broker such as Pusher or Redis. We chose Redis.

Continue reading
PHPUnit – Version Mismatch — February 4, 2019

PHPUnit – Version Mismatch

As our codebase matures we return to develop unit tests to ensure our QA process captures any code changes that may have altered the functionality of the product.

When calling PHPUnit on Windows or Linux we ran into some issues relating to the version of PHPUnit we had installed.

On Windows it was an ancient PHPUnit version 3 and on Linux It was running version 7. Neither of which were compatible with our Laravel 5.5 project which uses php version 7.0.

In order to use PHPUnit with our project we must use PHPUnit version 6 (see Supported Versions)

What I hadn’t realised is that we had installed PHPUnit both locally into the OS and with our Laravel project so it exists in composer.json and gets installed under the projects ./vendor. The version installed in the OS path is the version that isn’t compatible with our project, but because it’s in our path it’s taking precedence over our project installed version.

To run the project version we just need to be specific in how we call it.

$ ./vendor/phpunit/phpunit/phpunit
PHPUnit 6.5.13 by Sebastian Bergmann and contributors.

....F                                                               5 / 5 (100%)

Time: 345 ms, Memory: 16.00MB

There was 1 failure:

1) Tests\Unit\Finance\CostCodeTest::testApiGetCostCodes
Expected status code 401 but received 200.
Failed asserting that false is true.

/home/user/itsm/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestResponse.php:78
/home/user/itsm/tests/Unit/Finance/CostCodeTest.php:37

FAILURES!
Tests: 5, Assertions: 14, Failures: 1.

Laravel 5.5 HMR and Windows — January 15, 2019

Laravel 5.5 HMR and Windows

Using HMR in Chrome on Linux is faultless, but on Windows HMR fails to start in the browser.

Looking at the entries in the bowsers script tags they seem a bit goofy. There’s leading slashes and spaces before the script filename.

It seems this is a popular issue. We hunted around for quite a few pointers to resolve this.

https://github.com/JeffreyWay/laravel-mix/issues/1437

The only thing we changed was line 90 of Entry.js to add on the extra replace(/^\//, ''); A restart of yarn hot and a browser refresh and we were good to go. HMR and WDS show in the Chrome console as expected and changes to code are now dynamic.

Laravel Routing Returns 302 Redirect — November 20, 2018

Laravel Routing Returns 302 Redirect

That was a frustrating few hours. I added a route into my api.php route and every time I visited it it triggered a redirect. I saw nothing in the browser and it was like my controller function wasn’t even being called.

I put the usual dd() and dump() into my function and it wasn’t being called. I even changed the function name so it didn’t exist and thought I’d get an error message, but nothing – still a redirect.

What is going on? I expanded out the call from “Namespace\Class@function” to put in a function () {} and that was being called and ran ok.

Clearly there was something wrong with my class somewhere. The other functions within it worked fine, just this new one didn’t even seem to be there.

Sure enough a bit of Duck-Jitsu and I found this: https://stackoverflow.com/questions/35020477/laravel-unexpected-redirects-302 – answer 3 is where I got my clue.

I looked at my class constructor and I was using the auth middleware to ensure the functions were not used unless authenticated.

public function __construct() {
  $this->middleware(['auth:api']);
}

For my function I wasn’t using any authentication, so of course I was getting a redirect. But because it’s an api call and the expected return in JSON under an XHttpResponse I wasn’t able to properly debug it. I was getting back a HTML page instead.

As a work around all I needed to do was add in an exception for my function:

public function __construct() {
  $this->middleware(['auth:api'], ['except' => [ 'myFunction' ]]);
}

Now all I have to do is go back and sort out the authentication for the function so I don’t need to bypass it.

When is a Question Mark not a ? — August 29, 2018

When is a Question Mark not a ?

That’s a morning of smashing my face on the desk again. I deployed my dev program onto a production system and then started crying as it stopped working as it should.

It seemed that none of my query string parameters were making it through to the controller. I called up some debugging and dumped out my $request and $request->all() etc. and discovered that the parameters although shown in the browser dev window went AWOL between server and controller. On my dev environment it all acted as it should.

So there must be something different. PHP v7.2 on dev and v7.0 or production maybe? No, much simpler than that. None of the Laracasts and Laravel related Googling pulled up any particular clues. It wasn’t until I looked at Nginx and parameters not being passed to PHP that I got a hit.

https://serverfault.com/questions/685525/nginx-php-fpm-query-parameters-wont-be-passed-to-php

The answer was as simple as adding in the $is_args into my Nginx virtual server config.

location / {
  try_files %uri $uri/ /index.php$is_args$query_string;
}

Up until now I guess I’ve been using routing with the parameters as part of the URI. Now I’m using some query string parameters I need to put in the ?, which is the $is_args variable.

So why not a problem in dev? Because I’m not using Nginx, I just use artisan serve to debug my development program.

Laravel and Lagan Web Services – SOAP API — August 17, 2018

Laravel and Lagan Web Services – SOAP API

SOAP is a dirty word to me. But I have a need to interact with our CRM system to import / extract data. My go to platform for most of my PHP work is Laravel. So I looked at interacting with Lagan CRM using SOAP calls from PHP.

I started off accessing the Lagan WSDL pages to see what the capabilities of the API are.

http://[laganserver:8080]/lagan/services/FL?WSDL
http://[laganserver:8080]/lagan/services/FLAuth?WSDL
http://[laganserver:8080]/lagan/schema/FLService.wsdl

Now I can see the self documenting API calls I can make. I just need to create the SOAP envelope to pass data to the service with the call I want to make.

Continue reading

Laravel API and Bootstrap Form Validation — August 13, 2018

Laravel API and Bootstrap Form Validation

This caused me some grief today. I spent the day adding validation rules into my Laravel resource controller and rather foolishly set HTML5 validation parameters on my Vue.js / Bootstrap 4 form component.

Why foolishly?

Well if you follow the Bootstrap 4 JavaScript function to call form.checkValidity() you’re actually calling the HTML5 built in function. Not a Bootstrap function as I originally thought.

When Laravel validation failed at the resource controller and it pushes back 422 (Unprocessable Entity) and a json error object:

{"message":"The given data was invalid.","errors":{"name":["The name field is required."]}}

I thought Bootstrap was seeing the Laravel validation errors and flagging up fields as not valid. So I could not understand why one of my fields didn’t show as invalid when according to Laravel it was!

What I was actually doing was HTML5 validation and ignoring my Laravel validation response all together. With the API it’s best NOT to try to use both HTML5 and Laravel validation. You’ll get a confusing UX that uses a mix of browser error messages/popups and Bootstrap CSS error handling.

Make sure you add novalidate to your form tag – this ensures HTML5 browser validation is prevented at the form level.

To resolve the Laravel validation part I just use the Laravel json error object and DON’T USE checkValidity(), my axios .catch(error) processes the Laravel errors by calling showErrors(error.response.data)

 this.axios.post('/api/v1/mycall/',
   this.data
 ).then(() => {
   // That worked out well, do something.
 }).catch(error => {
   this.showErrors(error.response.data)
 })

 showErrors: function (error) {
  Object.keys(error.errors).forEach((field) => {
    let input = document.getElementById(field)
    input.classList.add('is-invalid') // Bootstrap invalid form input
  })
 }

This iterates through the json errors and adds the class is-invalid to the fields that Laravel tells me are invalid. This triggers Bootstraps CSS to show the field with a red border and unhides the form-control subsequent/child div that has a class of invalid-feedback

References

https://getbootstrap.com/docs/4.1/components/forms/#validation

When using Vue.js and Laravels json response the actual usage is closer to the server-side examples:

https://getbootstrap.com/docs/4.1/components/forms/#server-side

Laravel Debug Bar — August 6, 2018

Laravel Debug Bar

It’s a good job my job title doesn’t include development. Some things I find out seem a long time after I need then and would be useful in the world of development.

My latest discovery is the Laravel debug bar. https://github.com/barryvdh/laravel-debugbar

Using it means not so many dd() or dump() in my code to find out what Laravel sees. It even allows me to see the actual SQL queries being used.

Selection_073
Laravel Debug Bar

In many ways I wish I’d discovered this sooner.

Installation is very simple, just a composer require and then ensure your .env has the debug option enabled and/or debug bar enabled.

APP_DEBUG=true
DEBUGBAR_ENABLED=true

 

I Hate Internet Explorer — August 1, 2018

I Hate Internet Explorer

I just can’t seem to get it to die! Probably something to do with all those users out there spoiling my day and continuing to use it in Windows 7 – sadly in our corporate environment.

I’ve spent a few days with a problem showing “Syntax Error” in IE11 when my project runs just great in Edge, FireFox and Chrome.

After a lot of digging around in the components I’ve used I narrowed it down to one specific 3rd party component from npmjs. So I took a trawl through into the Github of the project and opened an issue. Turns out I’m not the only one seeing the problem. I guess everyone else is using more sensible browsers.

I got a great reply that pointed me to modifying my webpack config so it transpiled into IE11 compatible code. I’d already used polyfills like es6-promise and es6-object-assign and then babel-polyfill, but this obviously wasn’t enough.

The pointer was aimed at webpack. Now Laravel uses it’s own layer above webpack – Laravel-Mix, but with a bit more Googling I figured out how to add the necessary webpack config into mix to get it transpiling correctly.

Add the targets-webpack-plugin using yarn (or npm) as a “devDependency”

$ yarn add targets-webpack-plugin -D

In my webpack.mix.js file:

const TargetsPlugin = require('targets-webpack-plugin')

...

mix.webpackConfig({
  plugins: [
    new TargetsPlugin({
      browsers: ['last 2 versions', 'chrome >= 41', 'IE 11'],
    }),
  ]
})

Repackage my app with yarn run dev and IE11 is silent!

The internet is full of wonderfully helpful people. To those that post responses, write blogs or any kind of feed – I thank you all.

 

Axios and the X-CSRF-TOKEN —

Axios and the X-CSRF-TOKEN

When using Laravel it adds in some helpful headers to handle axios requests internally. But when it comes to sending requests externally you end up sending them the common headers added by Laravel and any plugins you may have added.

This typically includes the X-CSRF-TOKEN which your site uses to prevent Cross Site Request Forgery. But you don’t really want to send that out to external axios calls and in fact I’ve struggled as the sites I was using axios with returned a browser error because of it not being listed in their allowed headers:

SEC7123: Request header x-csrf-token was not present in the Access-Control-Headers-List.

SCRIPT7002: XMLHttpRequest: Network Error 0x8007005, Access is denied.

In order to resolve this you need to remove the headers from your axios call.

To do this I created a new instance of axios and returned that as my axios function.

let instance = this.$http.get(url, {
  transformRequest: function(data, headers) {
    delete headers.common['X-CSRF-TOKEN']
    delete headers.common['Authorization']
  }
})
return instance

I also added into here the removal of the Authorization header as that too should not go outside, and failed with the same message.

 

Laravel 5.5 and Bootstrap 4 — July 30, 2018

Laravel 5.5 and Bootstrap 4

Laravel 5.5 ships with Bootstrap 3. To make it use Bootstrap 4 you need to make a few changes.

resources/assets/js/bootstrap.js

Change require('bootstrap-sass') to require('bootstrap')

resources/assets/sass/app.scss

Change @import('~bootstrap-sass/assets/stylesheets/bootstrap'); to @import "~bootstrap/scss/bootstrap";

resources/assets/sass/_variables.scss

Change $font-size-base: 14px; to $font-size-base: 1rem;

Remove Bootstrap 3 using

$ npm remove bootstrap-sass

Install Bootstrap 4 using

$ npm install bootstrap

Rebuild your project

$ npm run dev