473,325 Members | 2,792 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

2 small Q's about mysql_real_escape_string()

Hi groupies,

I have 2 small questions, which i really want to be sure about:

- does mysql_real_escape_string() prevent any kind of mysq-injection?
- can it put escaped quotes etc in the DB, or are those automatically
'converted' again?

Thanks!

Frizzle.

Nov 7 '05 #1
9 1978
frizzle said the following on 07/11/2005 14:53:
Hi groupies,

I have 2 small questions, which i really want to be sure about:

- does mysql_real_escape_string() prevent any kind of mysq-injection?
As long as you use it appropriately. i.e. use it on all string-based
values, and don't allow user input to directly govern query string
structure. Other data-types should be explicitly cast before being used
in a query.

- can it put escaped quotes etc in the DB, or are those automatically
'converted' again?


mysql_real_escape_string() takes all instances of " and converts them to
\", and all instances of \ to \\, etc.

So if your input string already contains \", then it will be converted
to \\\" before being added to the query string. Therefore, (if used in
an INSERT statement), the database will then contain \". When SELECTING
this, the result will still be \".

--
Oli
Nov 7 '05 #2
Thanks Oli,

I don't completely understand your answers:
If i * always * use the mysql_real_escape_string() am i safe?
And with an insert statement, does it matter what kind of quotes i use
for the query?
Like
"Select * From 'bla' "
or
'Select * From \'bla\' ' ?

Thanks.

Frizzle.

Nov 7 '05 #3
frizzle said the following on 07/11/2005 15:34:
Thanks Oli,

I don't completely understand your answers:
If i * always * use the mysql_real_escape_string() am i safe?
Yes, if all you're doing is taking values from users and putting them
into appropriate places in an INSERT query, e.g.:

INSERT INTO table (name, age, class) VALUES ('XXX', 'YYY', 'ZZZ')

If you're allowing more complex interaction, e.g.

SELECT FROM table XXXXXXXXXX

where the user is allowed to specify the condition string, then
obviously mysql_real_escape_string() is of no use here, and you're very
much less than safe.
And with an insert statement, does it matter what kind of quotes i use
for the query?
Like
"Select * From 'bla' "
or
'Select * From \'bla\' ' ?


If you express a string in PHP code as:

$s = "This is 'some' text";

or

$s = 'This is \'some\' text';

the internal representation of $s will be:

This is 'some' text

in both cases. If you were then to do SomeFunction($s), it is this
internal representation that is used; how the string was originally
represented in code is no longer relevant.

Remember, PHP escaping is entirely separate from MySQL escaping. PHP
escaping is necessary to represent a string in PHP code, whereas MySQL
escaping is necessary to represent string-based data in a query string.

e.g. To insert:

This is 'some' text

into a MySQL database, the query string should be:

INSERT INTO table (field) VALUES ('This is \'some\' text')

and to represent that query string in literal PHP code, it should be:

"INSERT INTO table (field) VALUES ('This is \\'some\\' text')"
or:
'INSERT INTO table (field) VALUES (\'This is \\\'some\\\' text\')'
or:
"INSERT INTO table (field) VALUES ('"
. mysql_real_escape_string("This is \'some\' text")
. "')"
--
Oli
Nov 7 '05 #4
Oli Filth said the following on 07/11/2005 15:48:
"INSERT INTO table (field) VALUES ('"
. mysql_real_escape_string("This is \'some\' text")
. "')"


Oops, that last one should be:

"INSERT INTO table (field) VALUES ('"
. mysql_real_escape_string("This is 'some' text")
. "')"

--
Oli
Nov 7 '05 #5
Hmm, great, i understand what you mean.
With the real-escape part, i'm not using
the second case at all, so there's no problem
there.

Thanks for explaining all!

Frizzle.

Nov 7 '05 #6
On 7 Nov 2005 06:53:32 -0800, "frizzle" <ph********@gmail.com> wrote:
I have 2 small questions, which i really want to be sure about:

- does mysql_real_escape_string() prevent any kind of mysq-injection?
If you always remember to use it correctly, then it stops the class of SQL
injection attacks where values are interpreted as SQL.
- can it put escaped quotes etc in the DB, or are those automatically
'converted' again?


Well, that's one of its more common uses, to escape quotes in quoted strings
properly. Nothing's automatically converted, unless you have the dreaded
magic_quotes option turned on. So not sure what you're asking there.

I still maintain you're better off using a library such as ADOdb which
emulates placeholders for MySQL - not only do you not have to worry about
escaping any more, it also gets you into better habits for other databases
where using placeholders/bind variables is critical for efficiency.

http://adodb.sourceforge.net/
http://phplens.com/adodb/reference.f...s.prepare.html
--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Nov 7 '05 #7
Hmm,
somehow i can't get it to work correctly:

I tested inserting the following text:
'"\ (single quote, double quote, backslash)

Via PHP:
$new_var = mysql_real_escape_string($_POST['var']);
$put_info = mysql_query("INSERT INTO `test` ( `id` , `text` )
VALUES ('', '".$new_var."');");

And via phpMyAdmin.
If i view the first data via phpMyAdmin, i see the following:
\'\"\\

If i view the text inserted via phpMyAdmin, i see
'"\ (the way it should be)

What am i still missing? :s

Greetings Frizzle.

Nov 8 '05 #8
On 8 Nov 2005 07:15:33 -0800, "frizzle" <ph********@gmail.com> wrote:
Hmm,
somehow i can't get it to work correctly:

I tested inserting the following text:
'"\ (single quote, double quote, backslash)

Via PHP:
$new_var = mysql_real_escape_string($_POST['var']);
$put_info = mysql_query("INSERT INTO `test` ( `id` , `text` )
VALUES ('', '".$new_var."');");


Print out all the values involved to the browser so you can actually see what
is happening.

In my previous reply that you haven't quoted, I said:
Nothing's automatically converted, unless you have the dreaded
magic_quotes option turned on.


So, do you have magic_quotes turned on? If you don't know, use Google to find
out what it is. There is a chapter in the PHP manual about it.
--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Nov 9 '05 #9

Andy Hassall wrote:
On 8 Nov 2005 07:15:33 -0800, "frizzle" <ph********@gmail.com> wrote:
Hmm,
somehow i can't get it to work correctly:

I tested inserting the following text:
'"\ (single quote, double quote, backslash)

Via PHP:
$new_var = mysql_real_escape_string($_POST['var']);
$put_info = mysql_query("INSERT INTO `test` ( `id` , `text` )
VALUES ('', '".$new_var."');");


Print out all the values involved to the browser so you can actually see what
is happening.

In my previous reply that you haven't quoted, I said:
Nothing's automatically converted, unless you have the dreaded
magic_quotes option turned on.


So, do you have magic_quotes turned on? If you don't know, use Google to find
out what it is. There is a chapter in the PHP manual about it.
--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool


Sorry for not quoting, i only recently discovered where this option is
in
Google-groups...
Anyway, i checked the server's settings, and concerning magic quotes,
it
said the following:

magic_quotes_gpc On On
magic_quotes_runtime Off Off
magic_quotes_sybase Off Off

Thanks!

Frizzle.

Nov 9 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: leegold2 | last post by:
When I look directly in my db field I see a difference between these two functions. The top line (seebelow) was inserted with addslashes vs. the bottom line where I used mysql_real_escape_string....
1
by: Michael G | last post by:
If I only escape the characters that mysql_real_escape_string recognizes, is this adequate protection against SQL injection attacks? I have read a number of archived posts plus I've read some of...
2
by: Marcus | last post by:
Hello, My php.ini file currently has magic quotes set to On, but I have read that it is better to code with it off. Currently with magic quotes on, I only use stripslashes() to properly...
2
by: comp.lang.php | last post by:
when trying to use the mysql_real_escape_string() function, the following warning occurs: First of all, the user is not 'web' trying to connect to the database, secondly, what is...
2
by: matthud | last post by:
<?php //MAKE IT SAFE $chunk = $_POST; $title = $_POST; $url = $_POST; $tags = $_POST; $user = $_POST; $safe_chunk = mysql_real_escape_string(htmlentities($chunk)); $safe_title =...
11
by: zach | last post by:
I created a comment form which will inserts the comments into a database and displays them immediately. I want to make sure that its safe from users inserting unwanted data into the database or...
2
by: Pugi! | last post by:
It is by accident that I noticed that I forgot to use mysql_real_escape_string in part of my webapp. I tested input with following text : Hélène 51°56'12'' http://www.mysite.org/folder 3 functions...
13
by: ndlarsen | last post by:
Hello. It's been a while since I used php. Since then magic quotes has been deprecated and will be removed when php 6.0 hits. My question is, what should I be using when submitting data to a...
7
by: roseple | last post by:
Hi, can anyone please help me why I got this error every I uploaded files. Error: Here is the code on the said warning message: # Gather all required data $name =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.