Sanitize user input destined for database in PHP

Question

I have this code:

$query = "select id from votes where username = '$user' and article_id  = $this->id";

I tried this code to sanitize it:

$query = sprintf("select id from votes where username = '$user' and article_id = $this->id", 
    mysql_real_escape_string($user), 
    mysql_real_escape_string($password));

but I get this error for the mysql_real_escape lines:

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'mexautos'@'localhost' (using password: NO) in /home/mexautos/public_html/kiubbo/data/article.php on line 145 Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/mexautos/public_html/kiubbo/data/article.php on line 145 Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'mexautos'@'localhost' (using password: NO) in /home/mexautos/public_html/kiubbo/data/article.php on line 146 Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/mexautos/public_html/kiubbo/data/article.php on line 146

I get the user name here, I dont know if its safe enough:

function getUsername(){ return $this->username; }

Thx

whichdan · Accepted Answer · 2009-06-05 17:06:47Z

8

You need a mysql connection before you can use mysql_real_escape_string.

answered Jun 5, 2009 at 17:06

whichdan

1,8971 gold badge14 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jcslzr Over a year ago

I do have connection, I mean the site works and connects to the db, before inserting those lines. Thx.

whichdan Over a year ago

That's not what the error is saying! Try using your link identifier as the second argument for your mysql_real_escape_strings and see if that helps.

John Rasch · Accepted Answer · 2009-06-05 17:09:21Z

7

I would suggest using prepared statements for this instead of sprintf

answered Jun 5, 2009 at 17:09

John Rasch

63.6k19 gold badges109 silver badges141 bronze badges

2 Comments

jmucchiello Over a year ago

Great idea if the mysql interface supported them. He would need to switch to mysqli or PDO to use prepared statements.

John Rasch Over a year ago

If he's using PHP 5 or greater mysqli is included

Community · Accepted Answer · 2023-11-17 20:38:18Z

3

Not sure if this is what's causing your problem, but I believe the variables in your sprintf statement shouldn't be '$user' and '$this->id', but they should be '%s'

https://www.php.net/sprintf

edited Nov 17, 2023 at 20:38

CommunityBot

11 silver badge

answered Jun 5, 2009 at 17:07

JasonV

5966 silver badges13 bronze badges

3 Comments

Abinadi Over a year ago

Not to mention that he is trying to replace an article_id with a variable called $password.

Tomalak Over a year ago

Why use sprintf() at all - PHP has variable interpolation in strings. OTOH, a SQL statement built with sprintf() is just as unsafe as an interpolated one... Both methods should be avoided.

JasonV Over a year ago

@Tomalak - I know, but was merely highlighting a bug in his code, not proposing a better method.

Community · Accepted Answer · 2023-11-17 20:38:18Z

3

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'mexautos'@'localhost' (using password: NO)

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established

Did you check the link ? Is it active ? You need to be connected before to use mysql_real_escape_string() Don't you forget to set the password ?

Try:

mysql -u mexautos -p

(type Enter if no password)

Also, check out your sprintf() function, you need to use the %s to bind your variable

$a = 'Foo';
$b = 'Bar';
$foo = sprintf('Foo Bar %s %s', $a, $b);

edited Nov 17, 2023 at 20:38

CommunityBot

11 silver badge

answered Jun 5, 2009 at 17:10

Boris Guéry

47.6k8 gold badges56 silver badges88 bronze badges

1 Comment

Boris Guéry Over a year ago

Trying to connect throught the console is a way to check the privilege in raw way !

Ólafur Waage · Accepted Answer · 2009-06-05 17:08:25Z

2

You need a connection to use mysql_real_escape_string() because it uses the server's encoding type to help santitize.

Also the sprintf() should look something like this

$query = sprintf("SELECT id FROM votes WHERE username = '%s' and article_id = %d", 
    mysql_real_escape_string($user), 
    mysql_real_escape_string($password));

answered Jun 5, 2009 at 17:08

Ólafur Waage

70.2k22 gold badges147 silver badges199 bronze badges

1 Comment

JasonV Over a year ago

In mine, I wasn't sure if the id was necessarily an integer, so I just went with a string format.

n3rd · Accepted Answer · 2009-06-05 17:08:01Z

1

I'd recommend using a mature DB abstraction layer like Zend_Db (there are tons of them out there). Implementing your own homebrew solution is not something I'd recommend for a production system.

answered Jun 5, 2009 at 17:08

n3rd

6,0794 gold badges42 silver badges56 bronze badges

Comments

shazarre · Accepted Answer · 2009-06-05 17:15:08Z

0

Like the other said, not '$user' but '%s' and you need an open connection.

@Tomalak sprintf is faster - that's the reason why to use it - it is a native C function.

answered Jun 5, 2009 at 17:15

shazarre

2251 gold badge3 silver badges9 bronze badges

2 Comments

Paul Fisher Over a year ago

sprintf is faster than what? When interacting with the database, that's not an especially good reason...

shazarre Over a year ago

sprintf is faster than build PHP string interpolation, and it wasn't related whit the database subject, but whit Tomalak's comment.

Collectives™ on Stack Overflow

Sanitize user input destined for database in PHP

7 Answers 7

2 Comments

2 Comments

3 Comments

1 Comment

1 Comment

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

2 Comments

3 Comments

1 Comment

1 Comment

Comments

2 Comments

Related