Jump to content
MakeWebGames

learning mysql_real_escape_string just need to verify.


endo
 Share

Recommended Posts

I am learning some basic site protection now and i was wondering would anyone be able to tell me if this is the correct way to use the mysql_real_escape_string?

$db->query("UPDATE users SET crystals=crystals-{$_POST['crystals']} WHERE userid=$userid",
mysql_real_escape_string(users),
mysql_real_escape_string(crystals));
Link to comment
Share on other sites

Guest Anonymous

Re: learning mysql_real_escape_string just need to verify.

As both values are numeric - no... The clue is actually in the name : mysql_real_escape_string, however (for numerics) you should protect the SQL with other mechanisms - For example:

 

$sql = sprintf("UPDATE users SET crystals = crystals - %u WHERE (userid = %u)", @intval($_POST['crystals']), @intval($userid));
$db->query($sql);

 

Not ideal and certain individuals here will probably moan about optimization whilst missing the bigger picture, but it will work.

I'd also suggest a slight amendment:

 

$sql = sprintf("UPDATE users SET crystals = crystals - %u WHERE ((crystals >= %u) AND (userid = %u))", @intval($_POST['crystals']), @intval($_POST['crystals']), @intval($userid));
$db->query($sql);

 

Is better - as it protects you from going into the negative. Yes, you *could* check your in-game variables, but data does change rather rapidly, and unless you run your scripts to completion, you can never really be sure of what is actually happening at the database layer.

Link to comment
Share on other sites

Re: learning mysql_real_escape_string just need to verify.

Thanks again nyna but can anyone tell me how to use the mysql_real_escape_string? as this is helping me majorly :D im just learning on some basic protection, to hopefully secure my site against most hackers...... again major thanks to anyone who helps me here

Link to comment
Share on other sites

Re: learning mysql_real_escape_string just need to verify.

 

Thanks again nyna but can anyone tell me how to use the mysql_real_escape_string? as this is helping me majorly :D im just learning on some basic protection, to hopefully secure my site against most hackers...... again major thanks to anyone who helps me here

You use it if your escaping a string, not int.

So i'll use an example...

 

$SQL = sprintf("SELECT `ID` FROM `users` WHERE `username` = '%s'", 
mysql_real_escape_string($_POST['username']));

$db->query($SQL);

 

For more info: http://uk2.php.net/mysql_real_escape_string

Link to comment
Share on other sites

Re: learning mysql_real_escape_string just need to verify.

Understanding how you to use the function requires an understanding of what the function does.

mysql real escape string

mysql -- well, this is for use in mysql queries

real -- this part means that this function takes into account the current character set of the connection (not something you need to worry about too much from a programming point of view. It's just there to make sure things don't break in certain ways)

escape -- This is the heart of it. I'll explain more about this one in a second.

string -- As Nyna pointed it, this clues us into the fact that this function is intended to operate on strings. Worry not, if you pass a number to this, php converts it to a string. But, the point is that numbers have better methods for us to use.

 

Escape. When we escape something, we're taking a character that has a special meaning and rendering it neutral. Let's look at some PHP examples of escaping.

 

Suppose we wanted to put something like "$dollars" into a string, and we wanted it to litterally be $dollars, and not the variable $dollars.

It's the $ symbol that makes $dollars a variable. In order to neutralize that special meaning of the $ symbol, we use a \ symbol.

Our string would have to be written like this: "\$dollar" in order for that string to literally mean "$dollar".

 

Now, when it comes to MySQL, there's all sorts of special characters that have special meaning and we don't want users typing in these special characters and causing us problems.

By default, all of these special characters are not numbers. If you are dealing with a pure number, escaping the number does nothing for you because there is NO special character there.

 

So, to recap, mysql_real_escape_string() is used to neutralize special characters that might screw up our queries.

 

Now, in the first post we have this code:

<span class="syntaxdefault"><?php
$db</span><span class="syntaxkeyword">-></span><span class="syntaxdefault">query</span><span class="syntaxkeyword">(</span><span class="syntaxstring">"UPDATE users SET crystals=crystals-{$_POST['crystals']} WHERE userid=$userid"</span><span class="syntaxkeyword">,
</span><span class="syntaxdefault">mysql_real_escape_string</span><span class="syntaxkeyword">(</span><span class="syntaxdefault">users</span><span class="syntaxkeyword">),
</span><span class="syntaxdefault">mysql_real_escape_string</span><span class="syntaxkeyword">(</span><span class="syntaxdefault">crystals</span><span class="syntaxkeyword">));
</span><span class="syntaxdefault">?>
</span>

 

The biggest mistake here is in this line: mysql_real_escape_string(users),

 

users is considered a constant here. You have to use quotes around strings, if you pass a string to a function.

Then, the assumption that the column from the database table can be inserted into the function and escaped is backwards. PHP doesn't know what that is. What it does know is what is contained in the user's input.

$_POST['crystals'] could be passed into the mysql_real_escape_string() function, but not the table column crystals. It's already been said that crystals is a number that doesn't need to be escaped, but here we have a syntactical error in addition to the theoretical error of escaping numbers. ;)

Link to comment
Share on other sites

Re: learning mysql_real_escape_string just need to verify.

thanks Floydian I think the moderators or whoever has the ability to make this into a proper topic to do so as it is very helpful to gamers and lots of people as for help in this unfortunately i do not know any mods/admins unless nyna is one and im not sure if she is but hey if anyone can will they please do so. Thanks all members who have helped here.

Link to comment
Share on other sites

Re: learning mysql_real_escape_string just need to verify.

Hmm, I'm getting to understand the mysql_real_escape_string,

I've started on a Game Engine and just need to know bits and bobs,

Would I need to protect this?...

 

$result = mysql_query("SELECT * FROM settings");

while($row = mysql_fetch_array($result))
 {
 echo $row['game_name'];
 echo "
";
 }

 

What would be the code if you can protect this, I've tried different things.... But not working

Link to comment
Share on other sites

Guest Anonymous

Re: learning mysql_real_escape_string just need to verify.

 

Hmm, I'm getting to understand the mysql_real_escape_string,

I've started on a Game Engine and just need to know bits and bobs,

Would I need to protect this?...

 

$result = mysql_query("SELECT * FROM settings");

while($row = mysql_fetch_array($result))
 {
 echo $row['game_name'];
 echo "
";
 }

 

What would be the code if you can protect this, I've tried different things.... But not working

 

How the hell are you going to write your own game engine if you don't know such a thing?

it's more or less securing your inputs.

Link to comment
Share on other sites

Re: learning mysql_real_escape_string just need to verify.

 

Hmm, I'm getting to understand the mysql_real_escape_string,

I've started on a Game Engine and just need to know bits and bobs,

Would I need to protect this?...

 

$result = mysql_query("SELECT * FROM settings");

while($row = mysql_fetch_array($result))
 {
 echo $row['game_name'];
 echo "
";
 }

 

What would be the code if you can protect this, I've tried different things.... But not working

 

How the hell are you going to write your own game engine if you don't know such a thing?

it's more or less securing your inputs.

Yeah well I'm not that good securing my game, I've never tried securing any of my mods/games, I just think I know how to use mysql_real_escape_string, And I don't I really need to "no" that to create my own game, I've already started it and done the login page, register page and main page.

Link to comment
Share on other sites

Re: learning mysql_real_escape_string just need to verify.

 

Yeah well I'm not that good securing my game, I've never tried securing any of my mods/games, I just think I know how to use mysql_real_escape_string, And I don't I really need to "no" that to create my own game, I've already started it and done the login page, register page and main page.

Rofl, of course you need to know basic security to create a game, other wise, it will jsut be exploited / abused to hell.

Link to comment
Share on other sites

Guest Anonymous

Re: learning mysql_real_escape_string just need to verify.

 

INPUT is where the security is needed, If your input it safe, your output will be too

Erm.. I beg to differ...

We escape data going into the database -- that's the norm (at least it should be), however....

You *must* correctly escape data being echo'd/print'd as well. I won't present examples here (for obvious reasons), but a little simple experimentation may give you clue.

Link to comment
Share on other sites

Re: learning mysql_real_escape_string just need to verify.

What Nyna likely is talking about could be illustrated by saying (in a more helpful manner perhaps...) that securing user input against mysql injections is one thing, but securing the game against, for example, javascript code being inserted into a string displayed to users, perhaps in the form of a profile signature, that would not be remedied by the use of any database escaping function.

I'll save Nyna the trouble of pointing out that "that's not all there is to it".

Indeed, you're right. But that illustrates the point that the input can be stored in a database safely, but may still be unsafe to output back to the users.

Link to comment
Share on other sites

Re: learning mysql_real_escape_string just need to verify.

 

MTG how would you use htmlentities() / htmlspecialchars() and where?? :D

You'd use it for any string that's likely to be outputted, so lets say, for profile sig, they input

 

<script>alert("xss")</script>

 

This would be XSS, I normally escape the HTML when its fetched from the DB, though some replace HTML before it actually enters the database.

So..To secure this, its pretty simple...

 

echo $user['signature']; // insecure, the data in the database may contain html

echo htmlentities($user['signature']); // secure, the html being displayed is now replaced with entities

 

More info: http://uk3.php.net/htmlentities

Link to comment
Share on other sites

Re: learning mysql_real_escape_string just need to verify.

 

echo $user['signature']; // insecure, the data in the database may contain html

echo mysql_real_escape_string(htmlentities($user['signature'])); // NOW secure, the html being displayed is now replaced with entities and all quote marks and apostrophies are escaped

 

MTG how would you use htmlentities() / htmlspecialchars() and where?? :D

You'd use it for any string that's likely to be outputted, so lets say, for profile sig, they input

 

<script>alert("xss")</script>

 

This would be XSS, I normally escape the HTML when its fetched from the DB, though some replace HTML before it actually enters the database.

So..To secure this, its pretty simple...

 

echo $user['signature']; // insecure, the data in the database may contain html

echo htmlentities($user['signature']); // secure, the html being displayed is now replaced with entities

 

More info: http://uk3.php.net/htmlentities

Link to comment
Share on other sites

Guest Anonymous

Re: learning mysql_real_escape_string just need to verify.

*WHY* are you use mres() here? What's the point, you are just making a mockery of the whole concept of securing data.

It's NOT secure - It ~may~ appear to be, but there are a few instances that could prove - shall we say - entertaining to the less than honest individual.

Link to comment
Share on other sites

Re: learning mysql_real_escape_string just need to verify.

How come and i mean no offense here but people who know how to secure things really good IE(Nyna) never actually post the answer, i understand it would be more important to find things out yourself but to criticize someone who is trying to explain without doing so yourself is a little wrong is it not?

Please don't take this the wrong way its just what i think.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...