Queries with Superglobals

Sharif

When querying the database using superglobals, which is correct:

$sql = "SELECT username FROM users WHERE username = '$_POST['username']'";

$sql2 = "INSERT INTO users (username) VALUES ('$_POST['username']')";

$sql = "SELECT username FROM users WHERE username = '" . $_POST['username'] . "'";

$sql2 = "INSERT INTO users (username) VALUES ('" . $_POST['username'] . "')";

drawmack

the second way, but you are scrubbing that data first right?

LordShryku

2 is correct.

// will work
$sql = "SELECT username FROM users WHERE username = '".$_POST['username']."'"; 

// also will work
$sql = "SELECT username FROM users WHERE username = '{$_POST['username']}'";

// will not work 
$sql = "SELECT username FROM users WHERE username = '$_POST['username']'";

Tristan_Wells

If you use double quotes for storing the query in a varible or when running it through *_query then you can simple inclode the varible in single quotes.

Neither way would be incorrect. In my opinion there is no incorrect way of doing something provided it works. Its all about how YOU want to code something.

However, the first one would probably be both easiest to code and most effiecient.

EDIT: This is because number 2 fuses a few more strings than needed.

Sharif

Yes of course, I do:

foreach ($_POST as $key=>$value) 
{ 
	$_POST[$key] = htmlspecialchars(addslashes(trim($value)));
}

drawmack

what if their username is ' show tables

Sharif

No clue what I should do for that mack...

Lord Shryku,

Could I do this for INSERT as well:

$sql = "INSERT INTO users (username) VALUES({$_POST['username']})";

What the heck does {} do anyways?

drawmack

sharif - add slashes will fix that one in particula

you'll want to run a reg-ex that squashes dangerous characters though such as ; we just had a discusion on this in another thread.

Tristan_Wells

Originally posted by Sharif
No clue what I should do for that mack...

Lord Shryku,

Could I do this for INSERT as well:
$sql = "INSERT INTO users (username) VALUES({$_POST['username']})";
What the heck does {} do anyways? [/B]

{} can contain varibles if they are inside double quotes.

Thats how its used in the Invision Power Board skinning system anyway.

LordShryku

Uh huh. And it's been concluded: I need to learn more regex 🙁

Tristan_Wells

As Drawmack stated, Regex would be useful for preventing dangerous characers. Below is an example of that.

<?php
function regex_checkusername($name)
 {
   if(preg_match('^[a-zA-Z0-9-_]^',$_POST['username']))
    {
      return true;
    } else {
      return false;
    }
 }
?>

Then it could be used like this.

<?php
if(regex_checkusername($_POST['username']) == false)
 {
   echo('Your username was not valid!');
 }
?>

Sharif

While we're still talking about functions, how would I use this function that validates email addresses:

function validate_email($email)
{  

	$rc1 = (ereg('^[-!#$%&\'*+\\./0-9=?A-Z^_`a-z{|}~]+'.'@'.'[-!#$%&\'*+\\/0-9=?A-Z^_`a-z{|}~]+\.'.'[-!#$%&\'*+\\./0-9=?A-Z^_`a-z{|}~]+$', $email));  

	$rc2 = (preg_match('/.+\.\w\w+$/',$email));  

	return ($rc1 && $rc2);
}

if($validate_email($_POST['email']) == False)
{
                echo 'Your email address in not valid.';
                unset($_POST['email']);
}

Sharif

Also let me get this straight:

$sql = "INSERT INTO users (username) VALUES ('{$_POST['username']}')";

With the quotes and all: '{$_POST['username']}' ?

Can I do this:

$sql = "INSERT INTO users (password) VALUES ('{md5($_POST['password'])}')";