Does PHP mysql_real_escape_string () protect the database name?

Question

I know mysql_real_escape_string ()
adds a backslash to the following characters: \ x00, \ n, \ r, \, ', "and \ x1a

I know how this protects the request from being injected with something like a variable in the where clause. But here is a scenario I'm not sure about:

$query = "SELECT * FROM $db WHERE 1";

If $ db is taken from user input, the user can insert something like:
$ db = 'RealDatabase WHERE 1; DELETE FROM RealDatabase WHERE 1; SELECT FROM RealDatabase' 'RealDatabase WHERE 1; DELETE FROM RealDatabase WHERE 1; SELECT FROM RealDatabase' 'RealDatabase WHERE 1; DELETE FROM RealDatabase WHERE 1; SELECT FROM RealDatabase' ;

From my understanding, mysql_real_escape_string () will not affect this line by making the final query: $query = "SELECT * FROM RealDatabase WHERE 1; DELETE FROM RealDatabase WHERE 1; SELECT FROM RealDatabase WHERE 1";

which will delete the database. Is there another level of protection that I don’t know about?

Answer 1

The level of protection you are looking for is provided by backticks :

 "SELECT * FROM `$db` WHERE 1"; 

Backticks are used to qualify identifiers that might otherwise be ambiguous (e.g. MySQL reserved words ), and if you accept user input or have columns or bases with a variable name, you absolutely must use backlinks, or I can promise that You will run into problems in the future. For example, what if you had a system in which a temporary field name was created with some user input, only it turned out that the field received the name update ?

 "SELECT field1,field2,update FROM table;" 

He is failing. But:

 "SELECT `field`,`field2`,`update` FROM table" 

works just fine. (This is really a real example from a system that I worked on several years ago that had this problem).

This solves your problem in terms of entering bad SQL. For example, the following query simply returns an “unknown column” error, where test; DROP TABLE test test; DROP TABLE test - code of the introduced attack:

 "SELECT * FROM `test; DROP TABLE test`;" 

Be careful: SQL injection is still possible with backticks!

For example, if your $db variable contains data that had a downside in it, you can still add some SQL in normal mode. If you use variable data for the database name and fields, you must strip it of all backreferences before placing it in your statement, and then adjust it with the inverse conclusions once inside.

 $db = str_replace('`','',$db); $sql = "SELECT * FROM `$db` WHERE 1"; 

I use a database shell that has separate functions for disinfecting data and disinfecting database identifiers, and this is what the latter do :)

Answer 2

You really have to start your SQL queries.

This will protect you from a basic SQL injection. It comes down to the following:

(taken from PHP.net)

 $stmt = mssql_init('NewUserRecord'); // Bind the field names mssql_bind($stmt, '@username', 'Kalle', SQLVARCHAR, false, false, 60); // Execute mssql_execute($stmt); 

And PHP has support for related queries mainly across all databases. Oh, and, of course, you still need to sanitize all the input and output (display).

Additional information: - http://php.net/manual/en/function.mssql-bind.php

Answer 3

Instead of inserting the database name into the get request, you can create a separate table of database names and identifiers. Then add only id to the request. Then you can find the appropriate database name for this identifier and use this. Then you can make sure that the identifier obtained is numeric ( is_numeric ), and you can also be sure that the user can only select from the database that are in your list.

(In addition, this will prevent users from finding database names and possibly using them elsewhere in SQL injection on your site.)

Using the first method, you parse the database name before using it in your query and make sure that there are no spaces in it.

Answer 4

No, mysql_real_escape_string will not help you here. A function is not context-sensitive (it cannot be because it has no context), and this is a completely different threat model.

You need to go and check if the table exists without sending the table name entered by the user directly to the server. The best solution is to use a server table / lookup table containing the names of the tables that they are allowed to use. If they try to use something that is not there, then do not let them.

If you really need ALL tables, you can just ask the server "what tables do you have?" and run an exit through it (it’s not necessary to cache it for a certain period of time, so as not to ask the server each time), but there is a chance that in the end you will have a table that you do not want, and then orient yourself, and then you need everything use the array equally, so just go ahead and do it.

Answer 5

Since table names do not accept spaces, just separate them. This would do above $ DB RealDatabaseWHERE1;DELETEFROMRealDatabase.... This will invalidate the request, but prevent the flaw.

If you want to prevent such “hacker” things, just do explode(' ', $db) , then get an array of results [0]. This will get the first part (RealDatabase) and nothing more.

Answer 6

It’s best to use it anytime you use dubious data. If you yourself define a table and there is no room for fake, there is no need to avoid it. If your users decide everything that could potentially run as a request, avoid it.

Answer 7

If you really need to use get from the user (bad bad) for your database, then use the following coding style ...

 $realname = ''; switch ($_GET['dbname']){ case 'sometoken' : $realname = 'real_name'; break; case 'sometoken1' : $realname = 'real_name1'; break; case 'sometoken2' : $realname = 'real_name2'; break; case 'sometoken3' : $realname = 'real_name3'; break; case 'sometoken4' : $realname = 'real_name4'; break; case default : die ('Cheeky!!!'); } $query = "SELECT * FROM `{$realname}` WHERE 1"; 

or alternatively ...

 $realname = $tablenames[$_GET['dbname']]; if (!$realname) die ('Cheeky!!!'); 

Using these two methods or some similar coding will protect your input from unexpected values.

It also means that the user will never see the real names of tables or databases with which they can obtain information.

Make sure you check the contents of $ _GET ['dbname'] to make sure it is valid first, otherwise warnings will be issued.

I'm still saying that this is a very bad design, it is reminiscent of allowing users to provide the file name and pass it through the I / O functions without checking. This is just too dangerous to consider.

Safety is too important to use the rule of laziness.