Redemption Steps | Writing Secure Code

The simplest and safest redemption steps are to never trust input to SQL statements, and to use prepared or parameterized SQL statements, also known as prepared statements .

Validate All Input

So lets tackle the first step: never trust input to SQL statements. You should always validate the data being used in the SQL statement is correctly formed . The simplest way is to use a regular expression to parse the input, assuming you are using a relatively high-level language.

Never Use String Concatenation to Build SQL Statements

The next step is to never use string concatenation or string replacement to build SQL statements. Ever! You should use prepared or parameterized queries. Some technologies refer to them as placeholders or binding . The following examples show how to use some of the safer constructs.

Note	All these examples show that the connection information is not stored in the script; the code sample calls custom functions to get the data from outside the application space.

C# Redemption

 public string Query(string Id) {  string ccnum;  string sqlstring ="";  // only allow valid IDs (1-8 digits)  Regex r = new Regex(@"^\d{1,8}$");  if (!r.Match(Id).Success)  throw new Exception("Invalid ID. Try again.");  try {  SqlConnection sqlConn = new SqlConnection(GetConnnection);  string str = "sp_GetCreditCard";  cmd = new SqlCommand(str, sqlConn);  cmd.CommandType = CommandType.StoredProcedure;  cmd.Parameters.Add("@ID", Id);  cmd.Connection.Open();  SqlDataReader read = myCommand.ExecuteReader();  ccnum = read.GetString(0);  }  catch (SqlException se) {  throw new Exception("Error - please try again.");  } }

PHP 5.0 and MySQL 4.1 or Later Redemption

 <?php  $db = mysqli_connect(getServer(),getUid(),getPwd());  $stmt = mysqli_prepare($link, "SELECT ccnum FROM cust WHERE id = ?");  $id = $HTTP_GET_VARS["id"];    // only allow valid IDs (1-8 digits)  if (preg_match('/^\d{1,8}$/',$id)) {  mysqli_stmt_bind_param($stmt, "s", $id);  mysqli_stmt_execute($stmt);  mysqli_stmt_bind_result($stmt, $result);  mysqli_stmt_fetch($stmt);  if (empty($name)) {  echo "No result!";  } else {  echo $result;  }  } else {  echo "Invalid ID. Try again.";  } ?>

Versions of PHP prior to 5.0 do not support SQL placeholders like those shown in the preceding call to mysqli_prepare. However, if you use PEAR (PHP Extension and Application Repository, available at http://pear.php.net) to query databases, you can use query placeholders by calling DB_common::prepare() and DB_common::query().

Perl/CGI Redemption

 #!/usr/bin/perl use DBI; use CGI; print CGI::header(); $cgi = new CGI;  $id = $cgi->param('id'); # Valid number range only (1-8 digits) exit unless ($id =~ /^[\d]{1,8}$); print "<html><body>"; # Get connection info from outside 'web space' $dbh = DBI->connect(conn(),  conn_name(),  conn_pwd())  or print "Connect failure."; # error detail in $DBI::errstr $sql = "SELECT ccnum FROM cust WHERE id = ?"; $sth = $dbh->prepare($sql)  or print "Prepare failure"; $sth->bind_param(1,$id); $sth->execute()   or print "Execute failure"; while (@row = $sth->fetchrow_array) {  print "@row<br>"; } $dbh->disconnect; print "</body></html>"; exit;

Java Using JDBC Redemption

 public static boolean doQuery(String arg) {  // only allow valid IDs (1-8 digits)  Pattern p = Pattern.compile("^\d{1,8}$");  if (!p.matcher(arg).find())  return false;  Connection con = null;  try  {  Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");  con = DriverManager.getConnection(getConnectionInfo());  PreparedStatement st = con.prepareStatement("exec pubs..sp_GetCreditCard ?");  st.setString(1, arg);  ResultSet rs = st.executeQuery();  while (rs.next()) {  // Get data from rs.getString(1);  }  rs.close();  st.close();  }  catch (SQLException e)  {  System.out.println("SQL Error");  return false;  }  catch (ClassNotFoundException e2)  {  System.out.println("Execution Error");  return false;  }  finally  {  try  {  con.close();  } catch(SQLException e) {}  }  return true; }

ColdFusion Redemption

For ColdFusion, use cfqueryparam in the <cfquery> tag to make the query safer with parameters.

SQL Redemption

You really should not execute an untrusted parameter from within a stored procedure. That said, as a defense in-depth mechanism, you could use some string checking functions to determine if the parameter is correctly formed. The following code checks if the incoming parameter is made up of only four digits. Note the parameter size has been set to a much smaller size , making it harder to add other input.

 CREATE PROCEDURE dbo.doQuery(@id nchar(4)) AS  DECLARE @query nchar(64)  IF RTRIM(@id) LIKE '[0-9][0-9][0-9][0-9]'  BEGIN  SELECT @query = 'select ccnum from cust where id = ''' + @id + ''''  EXEC @query  END RETURN

Or, better yet, force the parameter to be an integer:

 CREATE PROCEDURE dbo.doQuery(@id smallint)

Oracle 10g adds POSIX-compliant regular expressions as does Microsoft SQL Server 2005. Regular expression solutions are also available for DB2 and Microsoft SQL Server 2000. MySQL supports regular expressions through the REGEXP clause. Youll find more information on all of these solutions in the upcoming Other Resources section.