Dynamic SQL: Building Your Database Query Using Strings

Database administrators prefer stored procedures (SQL Server) or prepared statements (MySQL) to dynamic SQL, but sometimes you need to use the latter in your web pages. Dynamic SQL is used in PHP coding to create WordPress plugins. However, you should be careful with these statements since they lead to hacked SQL injected sites if not secured properly. Although they leave a security risk, dynamic SQL is still useful as long as you take the added steps to protect from SQL injection hack attempts. You can write dynamic SQL using any language, but this article uses PHP for examples. Dynamic SQL is built using string variables, so just replace the PHP string code with your own.

New to PHP and SQL? Learn how to build websites at Udemy.com.

Normal SQL Statements

Before you can write dynamic SQL, you need to know how to write a SQL statement. The same languages used in stored procedures must be used for your dynamic SQL.  The following SQL code is a standard statement that retrieves a list of users from a “users” table:

select * from users

Pretty simple. The above query selects all columns and records. Typically, you have no need for all records and columns, so you filter them using the “where” clause and defining the columns you need to see. The following code gets a list of user records where the user’s Id is “4”:

select user_name, first_name, last_name from users where userid = 4

Turning SQL Statements into Dynamic SQL

Normally, you would add that statement to the database stored procedure or prepared statement and return the data set to your front-end code. But what if you need to build that statement dynamically in your code? You can use two PHP methods to protect your data and build dynamic SQL: using the “real_escape_string” function or use MySQLi prepared statements. Both of these methods protect your code from SQL injection while you build your statements.

Learn dynamic PHP, MySQL and JavaScript at Udemy.com.

An example of when you’d need to dynamically build SQL is when a user logs in. You take the user’s name and password and send it off to the MySQL server. This is also a common way to attack database servers. However, with the two above mentioned methods, you can protect against this issue.

The following code is an example of a small block of code that takes user input and builds dynamic SQL:

$sql = “select user_name, first_name, last_name from users where user_name=’” . $_POST[ ‘username’ ] . “ and password = ‘” . $_POST[ ‘password’ ] “’”;

The $_POST server variable indicates that you’re trying to retrieve a statement from a form post, which is usually a simple HTML web page with a login form. In this example, the text box names from the form are “username” and “password.”

The problem is that this string is also susceptible to SQL injection. SQL injection is beyond the scope of this article, but leaving a dynamic SQL query like the above without protection leaves your database and your website open to hackers. To protect against this hack, you add one line of code to your PHP.

The other problem is people with an apostrophe in their user name. For instance, a user could input “o’neal” into the username text box, and MySQL will innocently return an error. The apostrophe character denotes the beginning and the end to a string in MySQL (and SQL in general), so you must escape those characters even outside the realm of common script hacks.

The following code protects from SQL injection and names with apostrophes:

$sql = “select user_name, first_name, last_name from users where user_name=’” . $_POST[ ‘username’ ] . “ and password = ‘” . $_POST[ ‘password’ ] “’”;
$sql = $mysqli->real_escape_string($sql);

The above MySQLi function calls the “real_escape_string” method and escapes special characters such as quotes, apostrophes, return characters and NUL. This means that if a user innocently or maliciously enters those characters into your web page form, MySQL treats it as a literal and assumes it’s a part of the username or password.

Another option for dynamic SQL is using prepared statements. Prepared statements in PHP and MySQL work similarly to C# and SQL Stored Procedures. The prepared statement is “packaged” and escaped and you can pass variables as parameters. Dynamic SQL is a bit different with prepared statements. With prepared statements, you use the question mark character to indicate to MySQL where you want to pass parameters. Using the same type of query as the above, here is the SQL string statement to use with a prepared statement:

$sql = $mysqli->prepare (“select user_name, first_name, last_name from users where user_name = ? and password = ? ”);
$sql = bind_param (array($_POST[ ‘username’ ], $_POST[ ‘password’ ]);
$stmt->bind_result($result);

This is a bit more code, which is understandably the reason why coders choose not to go this route, but it’s more efficient and safe from SQL injection. The question marks also give you a better way to read your code instead of going through POST variables directly in a SQL statement.

You can technically build any part of your SQL statement, but the most susceptible are the values that take a string. If the value takes an integer, the hacker must work with a different script. Because you can just download scripts, hackers mainly go for easy forms such as a user name and password form, a form that takes only a few user input values and WordPress plugins. Since SQL statements are just strings, you build these statements using PHP string values. The following code uses 4 lines of code to build dynamic SQL:

$sql = “select user_name, first_name, last_name “;
$sql = $sql + “ from users where userid = “;
$sql = $sql + $_POST[ ‘userid’ ];
$sql = $mysqli->real_escape_string($sql);

Notice in this example, the dynamic SQL statement is built in the first two statements. The userid value is taken from a form POST and the last statement accounts for any SQL injection by escaping characters.

As you can see from the above code, you can build any SQL statement into a query. Just be mindful of possible hacks and always use the two mentioned functionalities to protect your website and database.

Learn how to write secure PHP code at Udemy.com.