escape strings to be set to mysql queries

Hi,

I have some strings I'll have to send to a php script to be used in sql statements. I have some concerns about single quotes, blackslashes, etc that may foul up the sql statement:

  "Hi, I'll use some single quotes ('')in this statement!"

If I try passing that as is I will meet certain doom. Is there some premade function in javascript to escape the characters that will give sql a problem?

Thanks
LVL 7
minnirokAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BogoJokerCommented:
Hi minnirok,

There is a premade function in PHP.  Probably mysql_escape_real_quotes() should do your bidding.
But in javascript?

I think it would be a function that replaces all ' characters with \'
I don't know if one already exists in javascript though.

Joe P
minnirokAuthor Commented:
Hi Joe,

Yeah I saw the one in PHP but thought it'd be better to let the client do this work instead of putting it on the server? Don't know. I could make my own char replacement function if I have to.
ZylochCommented:
There is no built in function for Javascript that I know of. You can probably build one yourself, especially for quotes with simple regex in Javascript. BogoJoker, I believe you mean mysql_real_escape_string() which is a mysql library function. A similar one could be PHP's addslashes()
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ZylochCommented:
As for your comment, minnirok about the client doing the work, it is infinitely better to have the server do the work. You can never trust the client since they could somehow put a process in between their escaping and the server receiving the data. Having the server do it means that the server last sees it and the user cannot do anything malicious anymore.
BogoJokerCommented:
Oop, Zyloch your right.  I always try and remember the name of that function and always end up going to php.net and finding it anyways.  Hehe, thanks:
http://www.php.net/manual/en/function.mysql-real-escape-string.php

Agree with Zyloch.  The server should always have the last say, its much safer, and MUCH more predicatable because you have 100% control.  With javascript who knows, the user might even have javascript turned off!

Joe P

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
minnirokAuthor Commented:
Ok fair enough, so I just use it like this right:

<?php

    $passedParam1 = "My name is 'guy', I am from NYC";
    $passedParam2 = "My dog's name is 'Indy'";

    $strSql = "SELECT * FROM users WHERE field 1 = '" . mysql_real_escape_string($passedParam1) . "' AND field2 = '" . mysql_real_escape_string($passedParam2) . "'";

    // then execute.
?>

Thanks
ZylochCommented:
Quite right. You may also be interested in an alternative way to use it. It is really the same thing, but sometimes it is more organized:

$strSql = sprintf("SELECT * FROM %s", mysql_real_escape_string($table_name));

Of course, you would never have something that simple, but it gives you the general gist. If you have a lot of user variables, you may want to think about using sprintf to organize it a bit more.
minnirokAuthor Commented:
I must be doing something wrong, just tried the following:

$str1= "hello there";

$str2 = mysql_real_escape_string($str1);
   
echo $str2;


but I don' get anything actually printed - just blank! What did I do wrong?
minnirokAuthor Commented:
whoops nevermind - should have read the whole documentation page - we have to have a connection to the db before trying to use the function.

Thanks everyone.
ZylochCommented:
Yep :-) mysql_real_escape_string is a mysql library function, not a PHP one.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JavaScript

From novice to tech pro — start learning today.