Best way to store articles in a database? (php and sql)

Question

I want to store articles in a database, but I can’t find a lot of information on how to do this, from what I read, it seems that between most people there is something that can be done efficiently. Many people will suggest a way, while others will point out problems with sql injections, and I cannot find much in this topic, which is quite new.

Here is the html article:

<div id="main"> <article> <header> <h3> Title </h3> <time pubdate="pubdate"> 2011-07-22 </time> </header> <p> Article Text </p> </article> </div> 

Ideally, I assume that it is best to store the html piece that makes up each article in a database, but it seems to have a lot of problems with this, and as I said, I can’t find many posts on this topic, and how who something new for php and databases, I want to get some input on a better way to do this before continuing.

Answer 1

Save your article as TEXT :) Just pass it through this php function first to prevent attack attacks:

 // Prevent MySQL Injection Attacks function cleanQuery($string){ if(get_magic_quotes_gpc()) // prevents duplicate backslashes $string = stripslashes($string); return mysql_escape_string($string); } 

Answer 2

When I ever store large user text, I am just base64, and then before displaying it, be sure to run it through htmlspecialchars, this will make html work, so htmlspecialchars(base64_decode($content)) will work fine for display.
If you use bbcode for formatting, be sure to run htmlspecialchars before starting formatting your bbcode.

This is not the only way, you can sanitize inputs without base64'ng, but I see no reason for this, especially when no one should see directly in the database.

Answer 3

Storing it in SQL db is fine, but you can and you must protect against SQL injection in your code.

those. clearing all user input before sending it to db.

PHP SQL Implementation Guide

Answer 4

I think the best way is to simply keep clear text, but this is usually not the case when you want to use additional formatting. You can convert html tags to bbcodes or similar tags that can prevent sql injection, however, if you avoid html content, it will be safe like any other content. so mysql_real_escape_string on any data that you put in the database, and everything will be fine.

However, it would be best practice to store the html code along with the text of the article as an html file, which you can use when the user requests data, but in the database you can simply save pure text for indexing and searching. This is ideal since you will not need html content to search anyway, and it will also prevent sql attacks if the content is purely text that should be stored in the database. But as the user requests a file, he receives the contents of the html file for this article, which contains formatted text and serves this.

Answer 5

use lucene or sphinx, either from Zend_Lucene, or through solr. they will make indexing for the article faster, and you can also do a full text search on them. Using lucene or solar to index and search in these cases is pretty much a standard procedure and will allow you to scale millions of articles.

sphinx is a daemon that runs in parallel with the mysql daemon. to use sphinx you can use the sphinx pecl extension.

if you want to go with lucene, you can try zend_lucene or solr, which is actually a tomcat distribution with webapp that provides lucene as a web service, so you can access it in a standard way regardless of language.

choosing any of them is ok. you can index full text (content) and categories, or whatever you need to index.

Answer 6

the safest way to prevent sql injection here is to use a prepared statement.

 $stmt = $con->prepare("INSERT INTO Articles (Title, Date, Article) VALUES (?, ?, ?)"); $stmt->bind_param("sss", $title, $currentDate, $articleBody); 

Question icons represent the meanings that you will go through. "sss" says that each of the three variables will be a string, and then you can call this prepared statement and pass the correct values ​​to it.

 $title = $_POST[title]; $currentDate = date("Ymd H:i:s"); $articleBody = $_POST[article]; $stmt->execute(); 

this will make sure that malicious sql cannot be entered into your database.

hope this helps!