Latest web development tutorials

SQLite injection

If your web site allows users to input, and input into the SQLite database, this time you are faced with a safety problem is known as SQL injection. This chapter will show you how to prevent this from happening, and to ensure the security script SQLite statements.

Injection usually occurs when requesting user input, such as user needs to enter the name, but the user has entered an SQLite statement, and this statement will be run on the database unconsciously.

Never trust user-supplied data, so only validated data processing, the rule is done by pattern matching. In the following example, the user name username is limited to alphanumeric characters or underscores, the length must be between 8-20 characters - according to need to modify these rules.

if (preg_match ( "/ ^ \ w {8,20} $ /", $ _GET [ 'username'], $ matches)) {
   $ Db = new SQLiteDatabase ( 'filename');
   $ Result = @ $ db-> query ( "SELECT * FROM users WHERE username = $ matches [0]");
} Else {
   echo "username not accepted";

To demonstrate this problem, consider this hypothesis excerpt: To demonstrate the problem, consider this excerpt:

$ Name = "Qadir '; DELETE FROM users;";
@ $ Db-> query ( "SELECT * FROM users WHERE username = '{$ name}'");

Function call to retrieve the column name specified by the user name matches the record from the user table. Under normalcircumstances, $ name contains only alphanumeric characters and spaces, such as string ilia.But here, to $ name added a new query, the calls to the database will cause catastrophic problems: injected DELETE query to delete all records of users.

Although there is not allowed to execute the query or database interface stacking multiple queries in a single function call, if you try to stack the query, then the call fails, but SQLite and PostgreSQL are still stacked in the query, which provides execution in a string All queries, which can cause serious security problems.

Prevent SQL injection

In scripting languages ​​such as PERL and PHP, you can skillfully handle all of the escape character. PHP provides programming language string functionssqlite_escape_string () for SQLite is used to escape special characters input.

if (get_magic_quotes_gpc ()) 
  $ Name = sqlite_escape_string ($ name);
$ Result = @ $ db-> query ( "SELECT * FROM users WHERE username = '{$ name}'");

Although the data is encoded so that the insert made safe, but it will render simple text comparison, in the query, the column contains binarydata, LIKE clause is not available.

Please note, addslashes () should not be referenced in SQLite query string, it will lead to strange results when retrieving data.