0

I am creating a magento module and and in the controller I am trying to generate a query. ex: "INSERT INTO ". $resource->getTableName('mymod/mymodtable')." SETpid='".mysql_real_escape_string($pp['id'])."'"; On my local setup this works ok, and I get the expected id in pid. But as soon as I upload it to my server, that portion becomes blank. I know that the database connection exists, because it inserts a new row with blank pid on server. I have tried var_dump and it does prove that $pp['id'] has the value, yet mysql_real_escape_string returns blank. I also tried mysqli_real_escape_string, but nothing. Any ideas?

Aoi
  • 839
  • 2
  • 13
  • 22
  • Any errors in your web server logs? – helion3 Feb 07 '14 at 19:56
  • Now is a good time to update the code to placeholders! Although, it is a rather .. odd .. issue. *If* the reported behavior is accurate, the only think I can think of is that the server has either a *broken* or a *compromised* setup. What values of `$pp['id']` have been observed failing? Are you observing "the blank" immediately after the escaping (with `var_dump`)? Have some minimal code to reproduce/observe the behavior (and, can you post such a minimal test-case as an accurate copy'n'paste code block)? – user2864740 Feb 07 '14 at 20:08
  • @helion3 Nope, no errors – Aoi Feb 07 '14 at 20:21
  • @user2864740 umm, since even I can't reproduce it outside the server, I cannot give a test block. But it's failing for any and all types of values in `$pp`, and even hardcoded values are failing. – Aoi Feb 07 '14 at 20:30
  • @user279988 No, I mean, *I want to see* the actual code you're using (for both the mysql_ and mysqli_ form) to display and verify the results .. in a single consolidated block of code. – user2864740 Feb 07 '14 at 20:31
  • At least in old-history, mysql_real_escape_string was connection-independent and, as such, was just a simple text replacement function. I cannot find the source for it now, however, and I suspect that it now uses a connection. If it *does* use a connection, then one other possibility can be checked: *does it work "locally" when connecting to the "remote" database*? – user2864740 Feb 07 '14 at 20:36

2 Answers2

0

Which database interface are you using? mysql_real_escape_string should be used only with mysql_query, and you shouldn't be using that interface if you can avoid it. Without a valid connection it may not function correctly.

When using mysqli you should be using parameterized queries and bind_param to add user data to your query. Calling the escaping function manually is usually a mistake.

If you're using Magento, you might want to look at how to escape values using the Magento database layer.

Community
  • 1
  • 1
tadman
  • 208,517
  • 23
  • 234
  • 262
  • I am using `mysql`, and `mysqli_real_escape_string` was just a test to rule out that condition. But what I don't understand is how does it work on my local setup, and not on server? – Aoi Feb 07 '14 at 20:09
  • Are you using MySQL the database or `mysql_query` specifically? Magento has its own database layer you should be using whenever possible. – tadman Feb 07 '14 at 20:12
0

Per this previous question, you probably don't have a "database connection".

What's often confusing is, that when the mysql_real_escape_string documentation talks about a database connection, they specifically mean a database connection opened with the mysql_connect function.

If you're using Magento's standard objects to talk to the database, you don't have a mysql_connect connection, you have a PDO connection (via a Zend_Db_Adapter class). PDO is designed to encourage parameterized queries.

So, your options here

  1. Go with the flow and build your queries using parameterized query strings. (if you can't figure out how a new questions with some code samples should set you right).

  2. The adapter object has a quote method which you can use to quote your strings

Community
  • 1
  • 1
Alana Storm
  • 164,128
  • 91
  • 395
  • 599
  • Thanks, I tried to use prepared statements like `$writeConn->query($qry,array('id'=>$pp['id']))`, which works fine, but with this method alone I can't pass a string type value with many special characters like strings containing both single and double qoutes, so `mysql_real_escape_string` is basically my lazy-man's approach to saving exact values always. What is the alternative for magento if I want to write my queries manually? – Aoi Feb 07 '14 at 21:55
  • @user279988 Yes, actually, you can. The idea is the `query` function and bound parameters are automatically escaped for you. This way you don't need to worry about escaping them, the system handles it for you. Give it a try. – Alana Storm Feb 07 '14 at 22:41