Secure way to compose a SQL query in PHP

[- Empty Shell -]

Hello everyone,
I can use the SQL queries in PHP programming language. It was very difficult to learn but at last I am successful in doing that. Now I want to know about the secure way for using the SQL query. Thought that you guys can help (as always).!! Please tell me about the secure way to compose a SQL query in PHP Since I don't know much about it, detailed information would be really grateful.

[Warner]

You can claim that the attacker must first obtain information on the scheme of the database, in most cases injections. True, but you never know how or when this information has filtered, and If this happens, your database will be in great danger. If you are using an open source, or a base that is public domain, or a pattern that belongs to a content management or forum, the hacker can easily obtain a copy of the code that you use. This may be a risk if it is a poorly conceived. So always try to do the best.!!

[Viensterrr]

There are some attacks that are mainly based on exploiting the code not being written with security. Never trust any data coming from the user, even if it comes from a menu, a hidden field or a cookie. Any data coming from the cookie can also attack the coding that you have done. So I would recommend you to use the wanted data only otherwise discard all the unknown data. MSSQL Server executes the SQL statements in the batch including a command to add a new user to the local database. If the application were running as "SA" and the MSSQLSERVER service available in sufficient privileges, the attacker now has an account with server access.

[CodGuru]

The following are some techniques for securing your SQL queries :

1. Never connect to a database as a superuser or the database owner. Use always customized users with very limited rights.
2. Verify that the data has the expected type. PHP has a range of validation feature wide, from the simplest of section variables and Section characters (eg is_numeric (), ctype_digit () respectively) with advanced Perl Regular Expression.
3. If the application waits for numerical input, consider verifying data with the function is_numeric (), or silently change its type using settype (), or with sprintf ().

[void]

I have provided you with an example of a more secure way to compose a query for paging, hope that you were looking for the same coding :

<?php
 
settype($offset, 'integer');
$query = "SELECT id, name FROM sales ORDER BY name LIMIT 20 OFFSET $offset;";
 
$query = sprintf("SELECT id, name FROM sales ORDER BY name LIMIT 20 OFFSET %d;",
                 $offset);
 
?>

[Modifier]

I am providing you with more steps that you should follow for securing while composing a SQL Query in PHP :

• Never post information specific to the database, including information on the scheme.
• You may have stored procedures and previously defined cursors to make users do not directly access tables or views, but this solution has another impacts.
• Quote each non numeric values that are passed to the database with the specific function to the database escape characters. If an escape mechanism to a specific database is not the function addslashes () and str_replace () can be very useful. Read the first instance. As the example shows, adding quotes to the static part of the complaint is not enough, making this application easy to hack.