Five Dimensions of Competency, redux

I have written in the past about the Five Dimensions of Competency as defined by Workforce Skills Qualification (WSQ). At the time, I was learning about WSQ and was about to be tested on my knowledge, so writing about it seemed a good way to really drive home the knowledge.

This theory seemed sound, until I found a better teacher - a real, living example.

This guy was a colleague, a fellow software developer who lasted exactly one year in the organization. And when you're a contractor, serving your first contract with the company to completion isn't that big a deal. In fact, if it's a renewable contract which doesn't get renewed, it's almost akin to a termination of services.

Now, I'm not saying this guy was lousy. He was young, energetic and possibly technically better than me. But as mentioned, competency is measured in five dimensions. Today, we're going to take a look at dimensions of competency where he did and didn't make the grade.

I'd like to say that this guy wasn't a bad guy. He was easy to get along with. Not terribly mature, but at the age of thirty there's plenty of room for growth.

1. Task Skills

Individually, this is where he shone. His technical knowledge was solid enough. He was certainly able to code. But of course, it has to be said that if software devs were judged solely on their ability to code, a huge portion of us would pass quite easily. The other stuff matters just as much, if not more.

The ability to write code.

Unfortunately, while his code worked, he had this tendency to be slipshod where code formatting was concerned. He didn't indent or space out his code properly, and even remarked to me on more than one occasion that these things were not important. He left rubbish comments all over the place - comments that made sense to him alone.

You know the kind of guy who writes "fixbug" in commit remarks? He was that guy.

Not only did he do all this, he didn't seem to understand why it was such a big deal. Not that I don't make mistakes, but when they're pointed out to me, I tend to know why they matter.

Knowledge: Pass
Skill: Pass
Attitude: Fail

2. Task Management Skills

Task Management Skills are mostly about prioritization, and here there's not much to say because as far as I can tell, he was never assigned anything substantial and therefore never had the need to prioritize.

Working overtime.

A plus point: he had no issues working overtime to get shit done - what was suspect was the need to put in that much overtime in the first place. Diligence wasn't the issue; how much of that diligence was productive, was the issue. I recognize this because I've had the same problem in the past.

Knowledge: ?
Skill: ?
Attitude: Pass

3. Contingency Management Skills

Most of our work was implementing new features, and fixing bugs. This guy could code, but sometimes his bug fixes didn't solve the problem, and even resulted in new bugs. He seemed to have an aversion to testing his code.

Bad patches and fixes.

I mean, we're talking about the really simple stuff here. If the bug is in a formula which is supposed to produce a result, isn't it reasonable to expect that if you applied a fix, you would test for the correct output?

Nope, he didn't do it. One of my strongest memories was asking the QA to reassign a bug fix to this guy because I was being swamped with work, saying "it's so simple, there's no way he can screw this up". The QA didn't look so sure, and after the "fix", I realized just why. The output was still wrong.

Now, at this point, this wasn't a matter of aptitude. His heart just wasn't in it.

Knowledge: Pass
Skill: Pass
Attitude: Fail

4. Job Management Skills

Now, this is where he really tanked. Where writing code was concerned, we had to ensure that our work was maintainable, and as mentioned earlier, he just didn't think stuff like appropriate comments, proper indentation and spacing, was all that important.

But that's just one thing.

You see, as professionals, we have obligations other than writing and testing code. As contractors, we also had to fill in our timesheets and clock our hours. He received multiple warnings for failing to do so.

Locking your workstation.

The most important area where he dropped the ball, was security. Not coding security, but basic security such as locking his screen when he went for a smoke break, or even leaving his docking station unlocked the entire damn day. None of these had anything to do with programming; it was just good old-fashioned sloppiness.

Knowledge: Fail
Skill: Fail
Attitude: Fail

5. Transfer Skills

Just like me, his coding skills were applied to different contexts. There were various parts of our work that required us to pivot to vastly different tasks. One day we could be working on the back-end, and another day we could be writing Stored Procedures and generating reports.

Using different tools.

The tasks I was assigned to eventually turned out even more different - APIs and DevOps as opposed to just front and back end programming - but that wasn't really his fault because those were assigned after he had departed.

Knowledge: Pass
Skill: Pass
Attitude: Pass

Conclusion

This entire exercise isn't about denigrating an ex-colleague just to look good. No, I was actually thankful for his presence. If it wasn't for a negative example to take reference from, I might have made some of these mistakes as well.

I know I've said it before, but this bears repeating - being a dev is not just about coding, and competency is not just about knowing how to code.

I have a lot to learn, and hopefully with more people making mistakes for me to observe, I'll learn a lot faster.

Competent regards,
T___T

The Case For Stored Procedures

I used to be a big fan of Stored Procedures. They kept me from worrying too much about SQL Injection and kept my code from getting too repetitive.

That's not to say I'm no longer a fan of Stored Procedures. I still love them, and still use them when the need arises. The thing is, I no longer overuse them. Yes, it's possible to rely too much on Stored Procedures. As I'm very fond of saying - there are no blanket solutions in this business. There's a time and place for everything. And Stored Procedures do have their place.

All the example code below is in PHP and SQLServer.

What's so special about Stored Procedures? 

Security? After all, Stored Procedures protect against SQL Injection, don't they? Yes, they do - to a point. However, parameterized queries do the same thing. In fact, the principle is exactly the same. Whether or not you use Stored Procedures, you should always use parameterized queries.

How about speed? Stored Procedures get compiled ahead of time and all that, so there's less time spent on re-parsing the query with repeated use. Well, you get pretty much the same thing with Prepared Statements.

Ease of maintenance? There's something to be said for that. Separating your logic into different tiers could accomplish that. But this is contentious. Sometimes you want all your logic to be in the same layer, ie your code.

With all of the above in mind, I'm going to outline exactly what Stored Procedures can do, that can't be done any other way.

Security 

Forget SQL Injection for a moment. What Stored Procedures do is execute queries on the tables in your database. With this, you can disable SELECT, UPDATE, DELETE access to all your tables, and grant only access to Stored Procedures. You can even specify which users get access to which Stored Procedures. This greatly limits what users can do to the database. And from a security point of view, that is unambiguously a good thing. A hacker can't simply insert an UPDATE or DELETE or even a SELECT statement into your database if all of these permissions are disabled. Only Stored Procedures are allowed, and since these Stored Procedures carry out very specific functions, they can't be used to attack your database.

Of course, if you're suicidal enough to write a Stored Procedure that drops all your tables, all bets are off.

Again, security

Look at this example...

<?php
$serverName = "serverName\sqlexpress";
$details = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$dbConn = sqlsrv_connect($serverName, $details);

$x="1";
$y="y";
$z="abc";

$strsql="UPDATE table1 SET field3 =? WHERE field1 =? AND field2=?";
$statement = sqlsrv_prepare($dbConn,$strsql,array(&$z,&$x,&$y));
sqlsrv_execute($statement);
?>

And look at this one calling a Stored Procedure. Unlike the first example, the tables and fields are not exposed to the application programmer. If you're not the database administrator, you don't know that the table you're updating is table1, or that the fields are field1, field2 and field3. You only know you're executing a Stored Procedure named sp_update!

This is excellent if you have different people working on the database and different people working on the server-side code, and want to limit knowledge of the database to a need-to-know basis.

<?php
$serverName = "serverName\sqlexpress";
$details = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$dbConn = sqlsrv_connect($serverName, $details);

$x="1";
$y="y";
$z="abc";

$strsql="CALL sp_update(?,?,?)";
$statement = sqlsrv_prepare($dbConn,$strsql,array(&$z,&$x,&$y));
sqlsrv_execute($statement);
?>

Well, if you use Stored Procedures, there's no longer a need to know.

Neatness 

Logical branching statements are possible in Stored Procedures. And this could be vital if you're a big fan of keeping your code lean. Take, for example, adding and updating a record in a table. Let's say your table named tb_students, and contains student information. Normally, one would have an UPDATE statement for updating the table and a INSERT statement for creating a record, like so. This assumes the variable id was obtained from a POST.

<?php
$serverName = "serverName\sqlexpress";
$details = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$dbConn = sqlsrv_connect($serverName, $details);

$name="John Smith";
$address="23 King George's Avenue";
$id=intval($_POST["id"]);

if (id==0)
{
    $strsql="INSERT INTO tb_students (name,address) VALUES (?,?)";
    $statement = sqlsrv_prepare($dbConn,$strsql,array(&$name,&$address));
}
else
{
    $strsql="UPDATE tb_students SET student_name =?, student_address=? WHERE student_id =?";
    $statement = sqlsrv_prepare($dbConn,$strsql,array(&$name,&$address,&$id));
}

sqlsrv_execute($statement);
?>

But wouldn't this be neater?

<?php
$serverName = "serverName\sqlexpress";
$details = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$dbConn = sqlsrv_connect($serverName, $details);

$name="John Smith";
$address="23 King George's Avenue";
$id=intval($_POST["id"]);

$strsql="CALL sp_updatestudents(?,?,?)";
$statement = sqlsrv_prepare($dbConn,$strsql,array(&$name,&$address,&$id));

sqlsrv_execute($statement);
?>

You could just call the Stored Procedure sp_updatestudents. If creating a record, just pass a "0" into the student_id field, and if updating an existing record, pass the student's id into the student_id field.

CREATE PROC [sp_updatestudents]
    @name VARCHAR(50),
    @address VARCHAR(150),
    @id INT
AS
    IF @id=0
        INSERT INTO tb_students (name,address) VALUES (@name,@address)
    ELSE
        UPDATE tb_students SET student_name =@name, student_address=@address WHERE student_id =@id;
GO

 

Portability between server-side languages 

Imagine if your code was in PHP and you wanted to make a switch to, say, C#. Hey, if your database calls were all in Stored Procedures, no problemo. Whether you're using PHP or C#, you still call the same Stored Procedure. The Stored Procedure still performs the exact same function on your database! But if your database calls were actually in the code itself, now you'd have to change all of them from PHP to C#.

Ouch.

Are Stored Procedures invincible? 

Not by a long shot. There are situations where use of Stored Procedures makes things sketchy (though we won't go into that here). But the reasons above are the strongest ones I have for using them. Your mileage may vary!

Thanks for reading. Stay tuned for the next UPDATE!
T___T

Stored Procedures vs SQL Injection

There's a myth going around that using Stored Procedures when querying your database, prevents SQL Injection.

That myth needs to be nipped in the bud. Or at the very least, qualified.

What is SQL Injection?

It'll take me too long to explain in detail. I'll just say - SQL Injection occurs when an SQL query accepts user input as parameters, and the input turns out to be malicious SQL snippets which compromise your database.

For examples and a fuller explanation, try this.

So you're saying Stored Procedures don't prevent SQL injection?

No, I'm not saying that. Nobody's saying that. Stored Procedures, like any tool, can be used or misused. Stored Procedures can prevent SQL Injection. It's a matter of how they're used.

Let's have an example of how a normal query can be compromised. In this case, x and y represent the user input.

UPDATE table1
SET field3 = 'z'
WHERE field1 = 'x' AND field2 = 'y';

So our table here is named table1 and this is its initial content.

field1	field2	field3
x	x	x
y	y	y
x	y	x
z	x	y
y	x	z
x	y	y

Running the query above would give you this result:

field1	field2	field3
x	x	x
y	y	y
x	y	z
z	x	y
y	x	z
x	y	z

Inject something into it. Try "newvalue';DELETE FROM table1;"

UPDATE table1
SET field3 = 'z'
WHERE field1 = 'x' AND field2 = 'newvalue';DELETE FROM table1;

And there you go. SQL Injection! This query first does an update, and then deletes the entire contents of table1!

UPDATE table1
SET field3 = 'z'
WHERE field1 = 'x' and field2 = 'newvalue';

DELETE FROM table1;

Now, say you have a Stored Procedure, named sp_update, that does the same thing. For the purposes of this exercise, we'll be using SQLServer.

CREATE PROC [sp_update]
    @x VARCHAR(50),
    @y VARCHAR(50),
    @z VARCHAR(50)
AS
    UPDATE table1
    SET field3 = @z
    WHERE field1 = @x AND field2 = @y;
GO

Now try injecting the malicious code "newvalue';DELETE FROM table1;".

CALL sp_select("x","y","newvalue';DELETE FROM table1;");

field1	field2	field3
x	x	x
y	y	y
x	y	newvalue';DELETE FROM table1;
z	x	y
y	x	z
x	y	newvalue';DELETE FROM table1;

See that? Instead of executing the DELETE command, the Stored Procedure actually saves the value into the table table1! That is because it's treating the input specifically as a parameter rather than part of the command. More on that later.

However...

What if you do this?

CREATE PROC [sp_update]
    @x VARCHAR(50),
    @y VARCHAR(50),
    @z VARCHAR(50)
AS
    EXEC 'UPDATE table1 SET field3 = ''' + @z + ''' WHERE field1 = @x AND field2 = ''' + @y + '''';
GO

The above is also a Stored Procedure. But like any other SQL query, it can be compromised. Why? Because it's not parameterized. The entire string is executed as-is. It's on par with simply running the original SQL string.

So, the conclusion is...

Stored Procedures, by themselves, don't prevent SQL Injection. Parameterization does. Parameterized Stored Procedures prevent SQL Injection.

In fact, parameterized queries prevent SQL Injection as well. Try the following example in PHP (using the sqlsrv extension):

<?php
$serverName = "serverName\sqlexpress";
$details = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$dbConn = sqlsrv_connect($serverName, $details);

$x="x";
$y="y";
$z="newvalue';DELETE FROM table1;";

$strsql="UPDATE table1 SET field3 =? WHERE field1 =? AND field2=?";

$statement = sqlsrv_prepare($dbConn,$strsql,array(&$z,&$x,&$y));
sqlsrv_execute($statement);
?>

field1	field2	field3
x	x	x
y	y	y
x	y	newvalue';DELETE FROM table1;
z	x	y
y	x	z
x	y	newvalue';DELETE FROM table1;

(You may want to read more about PHP's sqlsrv extension and prepared statements.)

Do you see what happened there? The same thing occurred with the parameterized Stored Procedure. It's actally parameterization that does the magic, not Stored Procedures per se.

Use both!

This may seem like overkill, but you can use both. Set up the Stored Procedure sp_update in your SQLServer database and then do the following:

<?php
$serverName = "serverName\sqlexpress";
$details = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$dbConn = sqlsrv_connect($serverName, $details);

$x="x";
$y="y";
$z="newvalue';DELETE FROM table1;";

$strsql="CALL sp_update(?,?,?)";

$statement = sqlsrv_prepare($dbConn,$strsql,array(&$x,&$y,&$z));
sqlsrv_execute($statement);?>

Note that the parameters for the array are in a different order here (ie, xyz instead of zxy), because we're using the Stored Procedure sp_update and have to abide by its specifications.

So why use Stored Procedures at all?

Well, there are a lot of nice security features that come with Stored Procedures, not just prevention of SQL Injection. But that's out of scope. Perhaps another time!

Know your security measures. Otherwise some mischievous hacker will make you SQueaL!
T___T

So you want to be a Web Developer? (Part 3/4)

So we've covered the front-end and back-end aspects of web development. And, as mentioned in the last segment, we're going to delve into databases.

Unless you're only planning to create static websites, which in this day and age isn't really feasible as a career choice, at some point or other you're going to have to deal with databases, or at least a data source. Some databases are more suitable than others - it all really depends on the scale of what you're trying to accomplish. An enterprise-level application might require SQLServer, whereas MS Access is probably better for a simple disposable solution.

Again, as with back-end scripting languages, learn one database platform, preferably two.

- MySQL (http://www.mysql.com/)
- SQLServer (http://www.microsoft.com/en-us/server-cloud/products/sql-server/)
- MS Access (https://products.office.com/en-SG/access)
- DB2 (http://www-01.ibm.com/software/data/db2/)
- Oracle (http://www.oracle.com/index.html)

 

What you must know

- SQL (well, duh)
- Views
- Indexing
- Stored Procedures
- Database security
- Normalization

Good-to-haves

The following aren't absolutely necessary, but if databases are going to be your specialty, you could do worse!

Do have a look at CSV, JSON and XML as alternative modes of data storage and transfer.

MS Excel is a spreadsheet application, but through the years it's picked up a heft amount of added functionality. Worth a gander. (https://products.office.com/en-us/excel)

LinQ is an alternative to SQL. (https://msdn.microsoft.com/en-sg/library/bb397926.aspx)

NoSQL databases such as MongoDB and Redis. They seem to be the in-thing now. For a more comprehensive listing of NoSQL databases, see this link (http://en.wikipedia.org/wiki/NoSQL).

What are my career options?

If you're going to do only databases, why go into web development at all? There's demand for database administrators all around.

Next

Coming up, the final ingredient needed to be a web developer. Stay tuned!