TinyMCE插入的Base64图片验证及MySQL存储技术问询

Hey there! Let's tackle your questions step by step based on the details you provided:

1. Is this a Base64 image instead of a regular link? #

Absolutely! The src attribute in your <img> tag uses the data URI scheme—that's the standard way to embed Base64-encoded images directly into HTML. Regular image links point to external file paths (like src="/assets/photo.jpg"), but your src starts with data:image/jpeg;base64,—that’s the definitive sign it’s a Base64-encoded image embedded right in the HTML, not a regular link.

2. How to verify it's a valid Base64 image? #

Try these simple methods to confirm:

• Visual check: If the src follows the pattern data:<image-type>;base64,<encoded-string>, it’s Base64 by definition.
• Browser preview: Copy the entire src value (from data:image/jpeg;base64,... to the end of the encoded string) and paste it into your browser’s address bar. Hit enter—if the image loads, it’s valid Base64.
• PHP script test: Write a tiny script to decode the data and render the image:

  <?php
  // Replace with your Base64 string (the part after "base64,")
  $base64_str = '/9j/4s/...';
  $image_data = base64_decode($base64_str);
  
  // Output the image
  header('Content-Type: image/jpeg');
  echo $image_data;
  ?>
  

  Run this script—if the image displays, you’ve confirmed the Base64 data is valid.

3. How to store this Base64 image in MySQL? #

Here’s a practical, secure approach:

Step 1: Prepare your database table #

Create a column to store the Base64 data. Use LONGTEXT as the data type (Base64-encoded images are ~30% larger than the original file, and LONGTEXT can hold up to 4GB of data, covering most use cases). Add a column to track the image type (like jpeg or png) for easier rendering later.

Example table query:

CREATE TABLE uploaded_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    base64_data LONGTEXT NOT NULL,
    image_type VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Extract Base64 data from the HTML #

Your $_POST['textarea'] contains the full <img> tag, so extract the Base64 part with a regex:

$html_content = $_POST['textarea'];

// Match jpeg, png, or gif Base64 images
preg_match('/data:image\/(jpeg|png|gif);base64,(.*?)"\s/', $html_content, $matches);

if (isset($matches[1]) && isset($matches[2])) {
    $image_type = $matches[1];
    $base64_data = $matches[2];
} else {
    die("No valid Base64 image detected in the content.");
}

Step 3: Insert into MySQL (using PDO for security) #

Use prepared statements to avoid SQL injection. Here’s a PDO example:

// Assume you have a PDO connection set up
$pdo = new PDO('mysql:host=your_host;dbname=your_db', 'username', 'password');

$stmt = $pdo->prepare("INSERT INTO uploaded_images (base64_data, image_type) VALUES (:data, :type)");
$stmt->bindParam(':data', $base64_data);
$stmt->bindParam(':type', $image_type);
$stmt->execute();

echo "Image stored successfully!";

Step 4: Retrieve and display the image later #

When you want to show the image from the database, fetch the data and build the <img> tag:

$image_id = 1; // Replace with your image's ID
$stmt = $pdo->prepare("SELECT base64_data, image_type FROM uploaded_images WHERE id = :id");
$stmt->bindParam(':id', $image_id);
$stmt->execute();
$image = $stmt->fetch(PDO::FETCH_ASSOC);

echo '<img src="data:image/' . $image['image_type'] . ';base64,' . $image['base64_data'] . '" alt="Stored Image">';

Quick performance note #

Storing Base64 images works great for small files, but for larger images, it’s often better to save the file to your server’s filesystem and store the file path in the database instead. This keeps your database smaller and improves user load times.

内容的提问来源于stack exchange，提问作者HDer