2

I have this peace of code :

$result = mysql_query("SELECT * FROM T_utilisateur WHERE cin = '" . $_POST['cin'] . "', AND pwd = '" . $_POST['pwd'] . "'");

My question is : Is there any way to write the string in the mysql_query() function in an easy way, because some times I have many variables that I have to concat whith the String.

In C# for example I'm using String.Format as he following : String.Format("SELECT * FROM T_utilisateur WHERE cin = '{0}', AND pwd = '{1}'", cin, pwd); which is an easy way , because I forget sometimes the char ' if I used the concat with .

user2280288
  • 85
  • 3
  • 8
  • 4
    You ought to be using parameterised queries, which would solve this problem and also protect you from SQL injection problems. – RichieHindle Apr 21 '13 at 16:17
  • Apart from not doing that for SQL queries without context escaping; yes, there are simpler ways to interpolate variables in double quoted string context. – mario Apr 21 '13 at 16:19
  • You can do similar in php - `$result = mysql_query("SELECT * FROM T_utilisateur WHERE cin = '{$_POST['cin']}' AND pwd = '{$_POST['pwd']}'");`, by using `{}` around arrays - just be aware of SQL injection by directly inserting posted values. – Sean Apr 21 '13 at 16:22

5 Answers5

4

Use prepared statements. They are designed for this purpose. Furthermore you should note that building SQL queries the way you are actually doing is vulnerable against SQL injections. Or in other words it this the best example for vulnerable code. What if a hacker posts SQL statements? Prepared statements will prevent from this as it separates query data from the query syntax.

In PHP you could use PDO for prepared statemtens (among other extensions). Here comes a short example:

$pdo = new PDO('...');

$stmt = $pdo->prepare('SELECT FROM table WHERE id=:id AND title=:title ...');
$stmt->execute(array('id' => 1, 'title' => 'test'));

Update:

In comments you said that you'll have to use the mysql_* extension. If doing so you should know, that you'll have to escape every single value to prevent from SQL injections. Like this:

 // first(!!!) connect to mysql. this is important because
 // mysql_real_escape_string() will need information about
 // the current connection's encoding to work properly
 if(!mysql_connect(...) && mysql_select_db(...)) {
     die('mysql connection error');
 }

 // escape values
 $id    = mysql_real_escape_string($_POST['id']);
 $title = mysql_real_escape_string($_POST['title']);
 ...

 // now you could just use double quotes `"` to insert vars into the query string:
 mysql_query("SELECT * FROM `table` WHERE id = $id AND title = '$title'");

If you need this additional info:

Note: There is a syntax called curly syntax using {} which can be used to access arrays in double quoted " strings. I've not used it in the example, as it is not recommended to place post vars in a query without escaping them first. However here comes a syntax example:

$array = array('foo' => 'bar');
$string = "Hello {$array['foo']}";
hek2mgl
  • 152,036
  • 28
  • 249
  • 266
  • I know about the PDO but I can't use it at exam, we have to work with the mysql_ method only – user2280288 Apr 21 '13 at 16:25
  • 1
    What?! Can I have the email address of your course leader? :) Tell him that hek2mgl has said to you, that mysql_* is deprecated and inject query vars the way you are doing is a BIG security hole – hek2mgl Apr 21 '13 at 16:26
  • hhh I did told him that, but he said to me that the best is to use mysql_ because there is some teachers who don't know about it and they may consider my answer as wrong – user2280288 Apr 21 '13 at 16:29
  • Ok, will post an example, but that's just to help you out in this situation. Don't try this at home! :) – hek2mgl Apr 21 '13 at 16:30
2

If you're using double quotes, any variable starting with $ will be filled in automatically by php. Not sure if it works with $_POST variables as well.

for ex:

$result = mysql_query("SELECT * FROM T_utilisateur WHERE cin = $cin AND pwd = $pwd");
giorashc
  • 13,691
  • 3
  • 35
  • 71
Mtvw
  • 336
  • 1
  • 7
  • 1
    To use array values in double quotes you use `{}` - `WHERE cin = {$_POST['cin']}` or `WHERE name = {$person['name']}` – Sean Apr 21 '13 at 16:25
1

The moment you use any user-provided value into an SQL query, you are exposed to SQL injections. Thus you must be careful and handle user-provided data appropriately. Best is to use PHP’s PDO for Database Access in your situation. This will also answer your question.

Here are a few good articles on the topic:

Beware that using mysql_real_escape_string is deprecated.

From php documentation:

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

Jean
  • 7,623
  • 6
  • 43
  • 58
  • As I said to @hek2mgl we are not able to use the PDO at exams – user2280288 Apr 21 '13 at 16:27
  • Then,you can use double-quotes as suggested by [hek2mgl](http://stackoverflow.com/users/171318/hek2mgl). Still, I wonder what kind of teacher would grade the use of deprecated APIs over PDO. – Jean Apr 21 '13 at 17:48
0

PHP has a sprintf() function, but you should be using a prepared statement for queries.

$result = mysql_query(sprintf(
  "SELECT * FROM `T_utilisateur` WHERE `cin`='%s' AND `pwd`='%s'", 
  $_POST['cin'], $_POST['pwd']
));
Mr. Polywhirl
  • 42,981
  • 12
  • 84
  • 132
0

I would say sprintf() if you want to do it in an insecure way. But honestly, as hek2mgl suggested, use prepared statements

juuga
  • 1,274
  • 1
  • 13
  • 25