Jacques1

The reason why prepared statements are secure is because they separate the query from the input data: You first send the query template to the database system. And then you send the input and execute the prepared statement. This two-stage scheme prevents the input from interfering with the query and causing an SQL injection. Unfortunately, the PHP core developers in all their wisdom decided to replace this scheme with so-called “emulated prepared statements”: They take the data, escape it, literally insert it into the placeholders and then send the whole thing as a plain query to the database system. In other words, this is not a prepared statement at all. It's just a kind of auto-escaping. This auto-escaping is much less secure than a prepared statement, because the input may very well interfere with the query. For example, the escape function may not work correctly due to encoding issues. So unless you have a very good reason for using this “feature” and fully understand the security implications, turn it off at all times. It should never have been the default.

What's the benefit? It's literally twice as much code and arguably less readable.

You don't need two loops. Either create the IN expression with array_fill() again or merge both loops into one. Numbered parameters also don't require a colon. Just use question marks and reference them by their number in the bindParam() call. Or even simpler, collect all values in an array and pass that array to execute(). Last but not least, be careful with the PDO configuration: By default, PDO replaces the prepared statements with escaped queries (which is much less secure). To use actual prepared statements, you have to explicitly disable PDO::ATTR_EMULATE_PREPARES. <?php $database = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'YOURUSER', 'YOURPASSWORD', array( PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, )); $type = 1; $ids = array(24, 25, 26, 27, 28, 29, 30); $people_stmt = $database->prepare(' SELECT name, id FROM people WHERE type = ? AND id IN ('. implode(',', array_fill(0, count($ids), '?')) .') '); // collect values $parameters = array(); $parameters[] = $type; foreach ($ids as $id) { $parameters[] = $id; } $people_stmt->execute($parameters); foreach ($people_stmt as $person) { var_dump($person); }

It's not necessarily bad practice, but doing it correctly is harder than you may think. How do you deal with multiple submissions from the same user? If you create a new CSV each time, how do you ensure unique filenames? If you overwrite the previous CSV, how do you make sure that simultaneous submissions don't trample each other? Note that file_put_contents() and fwrite() are not atomic, so you can end up with a mixture of both submissions. A correct implementation will be much more complex than a simple SQL query. And an incorrect implementation will be much less reliable. So personally, I'd use the database.

Whatever this is supposed to be, it's not PHP code. Where are the parentheses for the function call? Where are the quotes for the string? Where are the delimiters for the regex?

You still cannot pass an array to a parameter. This simply isn't possible with prepared statements, regardless of which database interface you happen to use. So you still need to create a separate parameter for each array element. However, PDO lets you skip the call_user_func_array() stuff, because you can simply call bind_param() for each parameter or pass an array to the execute() method.

I think you'll have to do your script kiddie password cracking bullshit somewhere else.

PDO is much easier to use than MySQLi. You don't need tons of low-level code and strange workarounds to get stuff done. I mean, the script above isn't even finished. Now comes the bind_result() and fetch() fun. In PDO, all you need is prepare(), execute() and a foreach loop to go through the result set. But more importantly, PDO is a universal interface for all mainstream database systems. Being limited to MySQL may be OK as long as that's all you know. But there are many other systems out there which you may want or have to use at some point. PDO let's you do that without forcing you to rewrite your entire code.

JSON.decode() is native JavaScript. Note, however, that some ancient browsers like IE 7 don't support it. If your site should run on those as well, you (again) need jQuery or some external JSON library. jQuery is a very lightweight and clear framework, most people pick it up intuitively without having to read any books. If you go through the basics and keep the documentation handy, you should be able to switch almost immediately.

Several things. First of all, the bind_param() call makes no sense. You have 8 parameters, but you only bind 4 variables to them: $id, $aa, $bb and $build_array. That last one is some strange comma-separated string. Not sure what this is supposed to do. Like mc_gyver already said, you need to use call_user_func_array() so that you can pass a variable number of arguments to bind_param(). You cannot call bind_param() directly. I also think the order of the parameter types isn't quite right. You have "ssss...ii", but shouldn't that be "siisss..."? The parameters for the IN operator come after the fixed parameters. It might be a good idea to approach this more systematically: Make one string for the types, one array for the parameter values, and fill them simultaneously: $id = 15; $aa = 1; $bb = 1; $datafeed_types = array(1, 2, 4, 7, 9); $query = ' SELECT datafeeds.id , datafeeds.val FROM datalog LEFT JOIN datafeeds ON datafeeds.id = datalog.did WHERE datalog.pid = ? AND datalog.aa = ? AND datalog.bb = ? AND datafeeds.type IN (' . implode(',', array_fill(0, count($datafeed_types), '?')) . ') '; $parameter_types = ''; $parameter_values = array(); // the fixed parameters first $parameter_types .= 'sii'; $parameter_values[] = $id; $parameter_values[] = $aa; $parameter_values[] = $bb; // the dynamic parameters for the datafeed types foreach ($datafeed_types as $datafeed_type) { $parameter_types .= 's'; $parameter_values[] = $datafeed_type; } // we need to pass references to bind_param $bind_param_args = array(); $bind_param_args[] = $parameter_types; foreach ($parameter_values as $i => $_) { $bind_param_args[] = &$parameter_values[$i]; } $stmt = $mysqli->prepare($query); call_user_func_array(array($stmt, 'bind_param'), $bind_param_args); $stmt->execute(); Too complex? Use PDO. Nobody said that MySQLi was a good idea.

C'mon, the code is almost plain English. It doesn't matter that it happens to be jQuery. I just use it as a kind of pseudo code, because writing down 20 lines of low-level JavaScript code only to do an Ajax request is fucking boring and doesn't clarify anything. I'm talking about a string within the JavaScript environment: var some_string = "foo"; var another_string = 'bar'; See my reply on your other thread.

I wrote down the exact PHP code in #17. Didn't you see that? The PHP script simply prints the JSON-encoded data: echo json_encode($data); It should also set the appropriate Content-Type header. The MIME type of JSON is application/json. So JavaScript makes an Ajax request to this PHP script. It gets back some JSON-encoded data. After it has decoded the response with JSON.decode(), it has a JavaScript object with all the data. And that was the point of this exercise.

You have three different languages nested in each other: A piece of JavaScript code within HTML markup within a PHP script. To me, this is the very definition of spaghetti code. Ideally, you shouldn't have any language nesting at all. The languages should be entirely separated with well-defined interfaces for exchanging data. For example: PHP scripts should only contain PHP code and nothing else (except maybe SQL queries). All HTML markup is kept in external template files. Those templates are managed with a template engine like Twig or Smarty. To pass values from PHP to HTML, you pass them to the rendering component of the engine. HTML documents should only contain HTML and nothing else. All JavaScript and CSS is kept in external files. To pass values from PHP to JavaScript, you use Ajax. JavaScript files should only contain JavaScript and nothing else, especially no HTML markup. Yes. I've already posted several code examples, but appearently that didn't help. Like I said in the other thread, I think you should try this out yourself.

You don't echo anything. That's the point. The Ajax request loads all your JSON data into a JavaScript object, and that's it. Now you can use your image paths. I think the problem is that you seem to be rather inexperienced with JavaScript. I don't mean this offensive, it's just my impression. I recommend that you take a few minutes of your time to learn to basics of jQuery (it's very simple) and then try things out yourself.

I'm not sure if it helps to start a performance discussion now. This may be a valid reason for using Ajax, but right now, I think we're dealing with more basic problems. The difference is how the data gets from PHP to JavaScript. Your way is the spaghetti code way: You have PHP code within JavaScript code within HTML markup, and you hope that your PHP output will eventually end up as a proper JavaScript object and neither confuse the HTML interpreter nor the JavaScript interpreter. As you've experienced multiple times, this doesn't work very well. You get strange backslashes, you get syntax errors and whatnot. It's time to draw the right conclusions: This approach is shit. So as you throw away the spaghetti code, you now need a way to fetch the data from JavaScript itself. And that's where Ajax comes into play. But do you also understand why you're using them? That's the key to the problem here.

The first thing I suggest is that you ditch this terrible inline scripting. No more onclick attributes, no more PHPHTMLCSSJavaScript spaghetti code. Your HTML document should contain HTML only. So all JavaScript, CSS and whatnot goes into external files. If you want to bind an event handler to an HTML element, you do that with pure JavaScript. In jQuery, it's as easy as calling the click() method. In plain JavaScript, you have to fumble with addEventListener() or attachEvent() depending on the browser. The data must also be fetched with pure JavaScript. Instead of making PHP dump some string into an onclick attribute, you use one of the techniques we've already discussed: Ajax or a div data container.

Your regex searches for span elements which are limited to one line (there are none in the document). Without the s modifier, a dot doesn't match linebreaks. However, when you add this modifier, you'll run into the next bug: Now you get everything from the opening span tag to the very last closing span tag somewhere at the end of the document. You might want to read up on greedy quantifiers and non-greedy quantifiers. But to be honest, the entire approach is awful. I understand that this is just some quick-and-dirty hack. But simply fetching all span elements without attributes in the hopes that those happen to contain your numbers seems a bit too quick and dirty. Look at the HTML markup of the page: There's actually a structure. I see one big container with the id “category”, I see main categories with the class “item”, and I see subcategories with the class “sub-item”. Those subcategories contain your numbers within a few other elements. So a smarter approach than the usual regex massacre is indeed to actually parse the markup and let the structure guide you to the data.

We need to see your code. However, it sounds like you're always running into the same problem: You try to inject some value from one language to another, you don't know how to prepare the value propely, and so the whole thing blows up. You already had that issue with the image paths, now you have it with some event attributes, and I fear you'll have it again in the near future. Maybe it's time to rethink your programming style. The thing is: Mixing different languages and injecting values from one language into the code of another language has always been a bad idea. It works to some extend as long as you're doing simple tasks with simple code. But appearently you're past this stage. A more modern approach is to avoid this PHPHTMLJavaScript inline scripting stuff altogether and put all JavaScript code into static external files. Instead of stuffing some PHP string into some onclick attribute, you fetch the data with JavaScript (using Ajax for example) and then assign an event handler to the button. For example: <!DOCTYPE HTML> <html lang="en"> <head> <meta charset="utf-8"> <title>JSON test</title> <script src="http://code.jquery.com/jquery-1.11.1.min.js"></script> <!-- Put this into an external script --> <script> $(function () { // load the data with Ajax $.getJSON('image_data.php', function (image_data) { // assign an event handler to the button; the handler has access to the data we've just retrieved $('#some-button').click(function () { alert('See the console for the data.'); console.log(image_data); }); }); }); </script> </head> <body> <button id="some-button">Click me</button> </body> </html>

Not sure why this is so hard to understand. You know how Ajax works, right? You make an HTTP request with JavaScript and get back the response as a JavaScript string. Well, and this string contains your JSON data. You didn't have to make any dubious code injections from PHP into some script element to get the data. You just fetched it with pure JavaScript. Why is this important? Because now the data cannot cause any trouble. No matter what it contains, it's just a JavaScript string. Think about my comparison again: Fetching the data with Ajax is similar to what a prepared statement does in the context of database queries. Yes, you can insert your PHP values directly into an SQL query. But then you have to be very careful that you escape the values correctly, and if you don't get it right, you end up with an SQL injection vulnerability (or at least a bug). A prepared statement doesn't have those problems, because you don't have to insert any values in the first place. So using Ajax is a good idea for the same reasons why prepared statements are a good idea – and I hope we all agree that prepared statements are far superior to manual escaping.

If the application still uses the old mysql_* functions, I'm sure there are other problems as well. What about SQL injection vulnerabilities? I think a demonstrated attack is more impressive than telling people that the MySQL extension will be removed in 20 years. If people cared about that, they would already have switched to PDO a long time ago.

Maybe it helps to compare this problem with a more common task: Let's say you want to pass a PHP value to a query. You have three options now: You just drop the value directly into the query. This is not a good idea, because the value may interfere with the query and cause bugs or even an SQL injection vulnerability. You escape the value and then insert it into the query. This is much better but somewhat fragile. You use a prepared statement and avoid the underlying problem altogether. This is by far the best solution. Going back to your actual problem, you're currently using the first option: You just drop the JSON string directly in your JavaScript code. This is not a good idea, because the data may interfere with the JavaScript code or the HTML markup (as demonstrated above). The second option would be to escape the data. Unfortunately, we cannot use standard HTML escaping within script elements, because HTML has special parsing rules for those. What we do instead is use a simple HTML element like div or span as a data container: We HTML-escape the JSON string, put it into the container and then read and parse it with JavaScript. I've demonstrated this in reply #5. By far the best option (the “prepared statement”) is to load the data with a separate Ajax request: <?php /* * The script for providing the image data. */ header('Content-Type: application/json'); echo json_encode(array( 'foo' => "/", 'bar' => 123, )); <!DOCTYPE HTML> <html lang="en"> <head> <meta charset="utf-8"> <title>JSON test</title> <script src="http://code.jquery.com/jquery-1.11.1.min.js"></script> <script> $(function () { // load the data with Ajax $.getJSON('image_data.php', function (image_data) { console.log(image_data); }); }); </script> </head> <body> </body> </html> Now there's no code insertion whatsoever. The PHP scripts provides the data as a JSON object, and JavaScript loads and parses it. This includes removing the backslashes.

Where the data comes from is irrelevant. Well, the first step is to let json_encode() do its job and not interfere with the default escaping. As I've explained in #3, there's actually a reason why the PHP core developers escape all forward slashes by default: Without this, the content of a JSON string may terminate the script element. This can happen accidentally or as part of a cross-site scripting attack. I'm not saying that you will run into problems if you leave out the escaping this time. Do we immediately crash into a tree and die if we don't put on the seatbelt? No, but we still put it on, because it's a sensible precaution. Frankly, I don't understand this obsession with hacky workarounds which only do the bare minimum required for the task. If your goal is to write robust code, you want the exact opposite: A solution which always works reliably, not just under specific circumstances. In that sense, Ajax is a very reliable solution, because it lets you avoid the escaping issue altogether: You echo the data with PHP, you parse it with JavaScript (which automatically removes the backslashes), and that's it. So? The data isn't secret, it's just not part of the website layout (unless you have a particularly geeky site).

If you don't know jQuery yet, you should learn it. It will save you a lot of time, and it's simply one of those tools every web developer should know. The two techniques I explained earlier are standard practices and part of the OWASP recommendations. Of course you don't have to follow best practices. If you think you can get away with a quick hack, well, go ahead. But if you're interested in a proper solution, it's a good idea to adhere to one basic rule: Escape all dynamic input. Don't try to distinguish between “safe” and “unsafe” values. It's a waste of time, it's unreliable, and it's missing the point. Even if a value is “safe”, it may still lead to bugs. Applying this rule to the problem at hand leads to one of the two solutions: Either you escape the data properly and put it into a hidden div container. Or you fetch it with Ajax and avoid the need for escaping altogether.

There's actually a whole lot of problems. First of all, you cannot make the constructor of a class return an instance of an entirely different class. This doesn't even make sense. If you never intended to instantiate your ConnectMe2Db class, then why have it in the first place? It seems you don't really know what you want. On the one hand, you've created that fancy wrapper class for MySQLi. On the other hand, you just use MySQLi directly without giving a damn about your class. On the one hand, you pretend to program object-oriented by instantiating the MySQLi class. But then you suddenly switch to the procedural interface and just use the MySQLi functions. Before you write a single line of code, get clear about what you want. What is the goal you want to achieve? How do you want to achieve it? Don't just start typing in the hopes that something useful will come out of it. Secondly, I have no idea why PHP programmers think it's a good idea to print all MySQL errors on the screen: or die (mysqli_error($this->dbconnection)). Do you realize that your users will see those messages on your website? What are they supposed to do with this? Help you debug your code? Actually, isn't it a rather bad idea to tell the whole world about your internal database structure? The error messages of MySQL are meant for you, the programmer, not your users. Fixing errors is your job. So please remove all this terrible mysqli_error() stuff and start using proper error handling. PHP can actually do that all by itself. You want MySQLi to raise an error message whenever there's a problem? Turn on error reporting in the MySQLi driver: $mysqli_driver = new mysqli_driver(); $mysqli_driver->report_mode = MYSQLI_REPORT_ERROR; $database = new mysqli(...); If you prefer exceptions over errors, append the MYSQLI_REPORT_STRICT flag. The error system of PHP automatically sends the messages to the right device according to your configuration. During development, you'll want to see the errors right on the screen for easier debugging, so you turn display_errors on and log_errors off. In your live system, you want to log the errors and not show them on the screen, so you turn display_errors off and log_errors on. You may also want an special error page for your users, but that's another topic. Last but not least, you need to learn how to use prepared statements with MySQLi. Manual escaping is obsolete (at least for the most part).

The script isn't processed until the element or the entire document has been parsed (depending on the defer attribute). See the spec.