If we wound the clock back several decades, not only would we find ourselves immersed in a time where fashion was non-existent (depending on your taste of course!) and Madonna was top of the charts, but we’d also be smack bang in the middle of some of the most exciting years in computing and internetworking.
In 1971, for example, the first Personal Computer (PC) was released to the world, with only 50 units produced in total; ten years later in 1981, the US giant IBM released its own Personal Computer version, with orders of magnitude more in number making their way off the production line. And the following year on the networking side, TCP/IP became standardized - signifying the precipice of truly digital global interconnectedness. Technological progression was positively rampant, and with an increasing number of devices connecting and generating data, the need to satiate the demand for efficient database construction and access increased also… a great segue to shift focus to this week’s blog post about PL/SQL and T-SQL!
SQL
IBM - the US giant behind the aforementioned PC - also oversaw the development of the Structured Query Language (SQL), which was designed to facilitate the creation, access, and management of databases. This move was certainly prudent given the exponential increase in data - and thus, data storage - that was to come, and, indeed looking at this in the modern-day context, it’s almost impossible not to comprehend the obvious advantages afforded to entities adopting data-driven decision-making approaches - approaches requiring systems that manage data accurately and efficiently.
Given the imperative of having a programming language that could handle the weight of the world’s data, IBM soon wasn’t the only company focused on innovation in database programming languages. And whilst there were more than a handful of SQL versions that emerged over the years, in this article, we’ll compare and contrast two of the most prominent ones - PL/SQL and T-SQL - both of which are integral to much of the world’s digital society.
SQL, PL/SQL, T-SQL: Similarities and Differences
T-SQL vs SQL
Created by Microsoft, T-SQL (Transact Structure Query Language) is an extension of SQL. Like PL/SQL, T-SQL is used to interact with relational databases. It also blends easily with SQL, meaning T-SQL code can contain SQL code. Nonetheless, T-SQL includes some features to provide more flexible control over application flows. These features, such as procedural programming elements, local variables, and special functions like the converted date, are not available in SQL.
PL/SQL vs SQL
PL/SQL (Procedural Language for SQL) is an extension of SQL that is used in the Oracle relational database. Since it is an application language, it can be utilized to build, format, and display user screens, web pages, and even reports. SQL, which is mainly used to manipulate data, provides the data for these applications. Like T-SQL, PL/SQL code can also contain SQL code within.
In PL/SQL, a code block - which is the smallest functional unit - is used to write a procedure or function. Moreover, the code executes as a whole block, making it different from SQL wherein code is executed as a single statement.
PL/SQL vs T-SQL
The major advantage of PL/SQL is that it allows the use of procedures that are similar to functions. This is why it is a procedural language, i.e., a language that defines how things need to be done. In contrast, T-SQL is a declarative language that defines what needs to be done rather than how.
PL/SQL provides functionalities for code iteration and decision-making. It also supports OOPS concepts like data encapsulation and function overloading. While these powerful functionalities can deliver huge advantages in development environments, they also make PL/SQL more complex and difficult to understand compared to SQL or T-SQL.
T-SQL is not only easy to understand and use, but it also provides programmers with a high degree of control. Moreover, they can define functions for string and mathematical operations, date and time processing, and error checking. These additions, plus procedural programming and changes to the DELETE and UPDATE statements all make T-SQL Turing complete. PL/SQL is also Turing complete, which is why both languages are universally accepted. In contrast to these extension languages, SQL is Turing incomplete.
Both languages offer unique features, making them suitable for a range of use cases.
Security Vulnerabilities in PL-SQL and T-SQL
Despite their many advantages, both PL/SQL and T-SQL can introduce security vulnerabilities into enterprise development environments. Developers should be cognisant of these weaknesses, and take steps to secure their code from threats and threat actors.
Some of the threats are:
- SQL injections: A vulnerability that allows attackers to use malicious SQL code to interfere with a web application’s queries to the database, and access information that’s not intended for display.
- Second order SQL injections: A vulnerability in which an attacker supplies malicious input into a database to be stored and later used in a new SQL query when a user accesses some other functionality of the same application.
- Code injections: An attacker is able to run code on the operating system.
- Use of Insufficiently Random Values: The software uses insufficiently random numbers or values in a security context that depends on unpredictable numbers.
- Weak Hashing Algorithm: Use of hashing algorithms that have been proven to be of high risk, or even completely broken, and thus are not fit for use.
- Plaintext storage of passwords: This makes passwords vulnerable to theft and exfiltration by bad actors, and exposes the organisation to data breaches and lateral movement into the network.
Hands-on Secure Coding Training in PL/SQL and T-SQL with SecureFlag
Security vulnerabilities are a part and parcel of modern-day development environments, whether the environment uses SQL, PL/SQL, or T-SQL. To stay on top of these gaps and prevent bad actors from harming the enterprise, it’s crucial to train developers in secure coding practices.
Unfortunately, too many organisations still rely on old-fashioned AppSec training approaches. The content in such programmes is rarely engaging and often outdated. Moreover, slideshows, videos, quizzes, and contrived examples rarely replicate real-world security issues. This is why 20-year-old security vulnerabilities - like SQL injections!! - still exist in code, and why developers still struggle to remediate them in real-world settings!
SecureFlag’s Adaptive AppSec training programmes address all these issues. Through 100% hands-on exercises leveraging real-world vulnerabilities, developers learn secure coding practices for PL/SQL and T-SQL. And since the training platform continuously evolves, developers can learn the most modern coding practices - all from within a comfortable training environment that’s personalised to match their skill and knowledge level.
SecureFlag’s training platform supports dozens of programming languages, including PL/SQL and T-SQL.
Contact us to know more.
