473,241 Members | 1,666 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,241 software developers and data experts.

Safe insert queries for mysql ?

Hi there,

I was wondering the folllowing: when i insert something
into a mySQL DB -in a guestbook for instance- i mostly use
mysql_escape_string($_POST['comment'). now i've seen
mysql_real_escape_string, and i was wondering if there's a
big difference between them, but most of all, i was wondering
if 'addslashes()' is safe enough, because i noticed that
stripslashes() doesn't strip all 'mysql_escape_string' slashes,
but does strip all 'addslashes()' ... :-s

I know there's something called mySQL-injection, and if i
got it correctly, that would mean executing queries
e.g. by submitting a " and then a query ...
of course i want to prevent this.

I hope this kinda makes sense ... :-)

Greetings Frizzle.

Sep 6 '05 #1
3 9274
frizzle (ph********@gmail.com) wrote:
: Hi there,

: I was wondering the folllowing: when i insert something
: into a mySQL DB -in a guestbook for instance- i mostly use
: mysql_escape_string($_POST['comment'). now i've seen
: mysql_real_escape_string, and i was wondering if there's a
: big difference between them, but most of all, i was wondering

The manual says that mysql_escape_string is deprecated, and replaced with
mysql_real_escape_string, which is basically "identical" in functionality.

mysql_real_escape_string is better because it considers the character set
of the database (connection?) to ensure that all the correct things are
escaped, where as the old function does not do that.

In other words you should replace mysql_escape_string with
mysql_real_escape_string, if you have the necessary version of php, and
everything should continue to work as before (though the new function
needs a database connection, so it is not quite a drop in replacement).

: if 'addslashes()' is safe enough, because i noticed that
: stripslashes() doesn't strip all 'mysql_escape_string' slashes,
: but does strip all 'addslashes()' ... :-s

You should use the escape routine that is specific to what you are doing
to be sure the correct things are escaped. I.e. If you are sending data
to a database you should escape using the database escape routine. If you
were sending literal data to html then you would escape using an html
escape routine. If you are using literal data in regular expressions then
use that escape routine (quotemeta() I believe), etc, etc...

(If you have the magic quote stuff turned on then you may end up escaping
some things twice, which is a bug you would want to fix, but that will not
normally be a security risk.)

--

This programmer available for rent.
Sep 6 '05 #2
On 6 Sep 2005 14:27:47 -0700, "frizzle" <ph********@gmail.com> wrote:
I was wondering the folllowing: when i insert something
into a mySQL DB -in a guestbook for instance- i mostly use
mysql_escape_string($_POST['comment').


Right, there's the problem to start with: "mostly use". At some point you will
forget to use it, and you've got a potential SQL injection attack opened up.

I recommend using the ADOdb library (http://adodb.sourceforge.net/). This puts
a wrapper around database access which handles escaping for you by emulating
placeholders - or using the database's native support if it has it (older MySQL
doesn't, newer MySQL does, as do other databases such as Oracle that it
supports).

Instead of using the raw MySQL calls and embedding values into SQL:

$res = mysql_query(
"insert into t (x, y) values (" .
"'" . mysql_real_escape_string($value_x) . "'," .
"'" . mysql_real_escape_string($value_y) . "'" .
")"
);

... you'd use the ADOdb functions such as:

$db->Execute(
'insert into t (x,y) values (?,?)',
array($value_x, $value_y)
);

The library does whatever is required to get the values into the database -
consistently and without you having to worry about quotes, escaping and so on.

If you follow the simple rule that you never put variables (or at least, user
input) into SQL statement strings, you always "bind" them separately like this,
it eliminates the vast majority of SQL injection issues.

--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Sep 6 '05 #3
Thanks for the answer!
I totally understand the first part!
Unfortunately, i don't quite understand the
last part about addslashes etc. Probably my
lack of knowledge of the English language, but
could you somehow try to explain it in an different
way?

thanks anyway!

Greetings Frizzle.

Sep 6 '05 #4

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

Similar topics

15
by: Jack | last post by:
I have a text file of data in a file (add2db.txt) where the entries are already entered on separate lines in the following form: INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great...
0
by: Rajesh Kapur | last post by:
I have a master slave configuration on linux machines running MySQL 4.0.21. Once every hour, a process deletes about 9000 rows and re-inserts fresh data on the master. The master process completes...
10
by: Python_it | last post by:
Python 2.4 MySQL-python.exe-1.2.0.win32-py2.4.zip How can I insert a NULL value in a table (MySQL-database). I can't set a var to NULL? Or is there a other possibility? My var must be variable...
3
by: Bob Bedford | last post by:
hello I'm looking for some functions or objects allowing to select-insert-update-delete from any table in a mysql database without the need to create a new query every time. Example: ...
9
by: David Eades | last post by:
Hi all Complete newbie here, so apologies if this is the wrong forum. I've been asked to use mysql and asp to make a simple bidding system (rather like a simple ebay), whereby users can use a...
2
by: javedna | last post by:
Hi Im doing a questionnaire in PHP and MYSQL I am trying to do a multiple insert using the following code $query1 = "INSERT INTO answers (answer_score,question_id,user_id) VALUES...
4
by: Agentmanatee | last post by:
I've searched through this forum as well as some other forums, no one else seems to have had this problem. maybe somebody knows what's going on: I got fed up with my hosting service's limiting my...
4
bugboy
by: bugboy | last post by:
I'm inserting a new word into table 'w' and a definition into table 'c' which are linked in table 's' which is the relation table for the many to many relationship between 'w' and 'c'. I've been...
6
by: Chuck Anderson | last post by:
Granted, this is mostly a mysql question, but I think understanding the Php makes it easy to understand why my question is necessary. I'm writing a Php loop to create INSERT and UPDATE queries...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...

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.